Learn something new every day. I could have sworn that CHAR columns were always padded with trailing spaces. Not so! SQL BOL for
ANSI_PADDING describes what is actually going on, but it is one of those things where I say "Yeah. Whatever. Not important." and skip right over it.
BOL states the following:
For ANSI_PADDING ON char(n) columns are padded.
<== Ok. Expected behavior.
For ANSI_PADDING OFF and char(n) NULL:
"Follows same rules as for varchar or varbinary when SET ANSI_PADDING is OFF." <=== hmmm... what does that mean?
For ANSI_PADDING OFF and varchar(n):
"Trailing blanks in character values inserted into a varchar column are trimmed. Trailing zeros in binary values inserted into a varbinary column are trimmed." <== Now that's interesting. Maybe should have read a little closer.
What prompted this post was first a tweet by
@chuckboycejr about a new blog post by Linchi Shea
here.
Jack Corbett aka
@unclebiguns Posted the following comment:
"Interesting find. I wouldn't think that this would/should happen in your example because ANSI_PADDING should not affect the char() columns anyway. If you had used varchar() columns I wouldn't have been surprised by this behavior." My initial response was to agree. Then I dug into BOL and some sample code a bit further. What I found was unexpected.
I was wrong when I thought CHAR columns were always padded. If a CHAR column is created when ANSI_PADDING is OFF, trailing spaces are automatically trimmed. This matches what BOL says but it didn't break through my mental barriers and false assumptions until I saw the results in SSMS.
Here is the code (have only tested on SQL Server 2008 Dev Ed):
SET ANSI_PADDING ON;
CREATE TABLE MyTable (col_ch_on char(25) NULL);
SET ANSI_PADDING OFF;
ALTER TABLE MyTable ADD col_ch_off char(25) NULL;
-- INSERT a single row the string 'Test String' into both CHAR
-- columns with three (3) leading and trailing spaces.
INSERT MyTable (col_ch_on, col_ch_off)
VALUES
(
' Test String ', -- col_ch_on
' Test String ' -- col_ch_off
);
SELECT 1 Sort,
'col_ch_on' ColName,
col_ch_on ColValue,
LEN(col_ch_on) ColLen,
DATALENGTH(col_ch_on) ColDataLen,
'X' + col_ch_on + 'X' ColConcat,
REPLACE(col_ch_on, ' ', '*') ColReplace
FROM MyTable
UNION
SELECT 2 Sort,
'col_ch_off' ColName,
col_ch_off ColValue,
LEN(col_ch_off) ColLen,
DATALENGTH(col_ch_off) ColDataLen,
'X' + col_ch_off + 'X' ColConcat,
REPLACE(col_ch_off, ' ', '*') ColReplace
FROM MyTable
ORDER BY Sort;
DROP TABLE MyTable;
Yields the following results:
| Sort | ColValue | ColName | ColLen | ColDataLen | ColConcat | ColReplace |
| 1 | col_ch_on | Test String | 14 | 25 | X Test String X | ***Test*String*********** |
| 2 | col_ch_off | Test String | 14 | 14 | X Test StringX | ***Test*String |
Now isn't that interesting!
Not sure if this has any affect on Linchi Shea's post or not. I will try and do some more research.