In previous posts I discussed the
finding and
fixing of leading and trailing spaces in variable character columns (varchar and nvarchar) in SQL Server 2008. In the posts I discussed the automatic trimming of trailing spaces based upon the ANSI_PADDING setting. In this post I will discuss and show the behavior of ANSI_PADDING, variable characters columns, and trailing spaces.
To start with, leading spaces are never automatically trimmed for both varchar and nvarchar. Also, for nvarchar columns trailing spaces are never automatically trimmed - ANSI_PADDING has no affect on nvarchar columns. For varchar columns the value of ANSI_PADDING when the varchar column was created comes into play - if ANSI_PADDING was ON when created, a varchar column behaves the same as nvarchar and trailing spaces are not automatically trimmed; if ANSI_PADDING was OFF when the varchar column was created then trailing spaces are automatically trimmed.
Following is a simple script that shows this behavior:
SET ANSI_PADDING ON;
CREATE TABLE MyTable (col_vc_on VARCHAR(100), col_nvc_on NVARCHAR(100));
SET ANSI_PADDING OFF;
ALTER TABLE MyTable ADD col_vc_off VARCHAR(100);
ALTER TABLE MyTable ADD col_nvc_off NVARCHAR(100);
-- The following query shows that the ANSI_PADDING setting
-- only affects varchar columns and not nvarchar columns.
SELECT t.name TableName,
c.name ColName,
st.name ColDataType,
CASE CAST(c.is_ansi_padded AS VARCHAR(3))
WHEN '0' THEN 'OFF'
WHEN '1' THEN 'ON'
ELSE NULL END ANSI_PADDED
FROM sys.columns c JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.types st
on c.system_type_id = st.user_type_id
WHERE t.name = 'MyTable';
-- INSERT a single row using same test string with
-- three (3) leading and trailing spaces.
INSERT MyTable (col_vc_on, col_nvc_on, col_vc_off, col_nvc_off)
VALUES
(
' Test String ', -- col_vc_on
' Test String ', -- col_nvc_on
' Test String ', -- col_vc_off
' Test String ' -- col_nvc_off
);
-- Following query shows leading spaces are never automatically
-- trimmed. Trailing spaces are only ever trimmed when a varchar
-- column was created with the ANSI_PADDING setting OFF. Trailing
-- spaces are never trimmed from nvarchar columns. Trailing spaces
-- are not trimmed from varchar columns if ANSI_PADDING was ON
-- when varchar column was created. The Sort column was used to
-- ensure the order of the resultset matched the order of column
-- creation. The resultset includes several different views of each
-- columns data including LEN, DATALENGTH, concatentation of an X
-- on front and end, and REPLACE of spaces with an asterisk.
SELECT 1 Sort,
'col_vc_on' ColName,
col_vc_on ColValue,
LEN(col_vc_on) ColLen,
DATALENGTH(col_vc_on) ColDataLen,
'X' + col_vc_on + 'X' ColConcat,
REPLACE(col_vc_on, ' ', '*') ColReplace
FROM MyTable
UNION
SELECT 2 Sort,
'col_nvc_on' ColName,
col_nvc_on ColValue,
LEN(col_nvc_on) ColLength,
DATALENGTH(col_nvc_on) ColDataLen,
'X' + col_nvc_on + 'X' ColConcat,
REPLACE(col_nvc_on, ' ', '*') ColReplace
FROM MyTable
UNION
SELECT 3 Sort,
'col_vc_off' ColName,
col_vc_off ColValue,
LEN(col_vc_off) ColLen,
DATALENGTH(col_vc_off) ColDataLen,
'X' + col_vc_off + 'X' ColConcat,
REPLACE(col_vc_off, ' ', '*') ColReplace
FROM MyTable
UNION
SELECT 4 Sort,
'col_nvc_off' ColName,
col_nvc_off ColValue,
LEN(col_nvc_off) ColLen,
DATALENGTH(col_nvc_off) ColDataLen,
'X' + col_nvc_off + 'X' ColConcat,
REPLACE(col_nvc_off, ' ', '*') ColReplace
FROM MyTable
ORDER BY Sort;
DROP TABLE MyTable;
First SELECT shows the following resultset and confirms that the ANSI_PADDING setting has no affect on nvarchar columns:
| TableName | ColName | ColDataType | ANSI_PADDED |
| MyTable | col_vc_on | varchar | ON |
| MyTable | col_nvc_on | nvarchar | ON |
| MyTable | col_vc_off | varchar | OFF |
| MyTable | col_nvc_off | nvarchar | ON |
The second SELECT shows the expected resultset - leading spaces are never trimmed, nvarchar is not affected by ANSI_PADDING and trailing spaces are never automatically trimmed, varchar columns automatically trim trailing spaces if ANSI_PADDING was OFF when created but not if ANSI_PADDING was ON:
| Sort | ColName | ColValue | ColLen | ColDataLen | ColConcat | ColReplace |
| 1 | col_vc_on | Test String | 14 | 17 | X Test String X | ***Test*String*** |
| 2 | col_nvc_on | Test String | 14 | 34 | X Test String X | ***Test*String*** |
| 3 | col_vc_off | Test String | 14 | 14 | X Test StringX | ***Test*String |
| 4 | col_nvc_off | Test String | 14 | 34 | X Test String X | ***Test*String*** |
The LEN() function automatically trims trailing spaces. The DATALENGTH() function shows "true length" (note that the nvarchar columns are twice the size of the varchar columns - 2 bytes per character to support UNICODE characters rather than 1 for ANSI).
Will shortly complete the post to show how to prevent leading and trailing spaces from appearing in variable character columns.
Please share any feedback that you may have.