I ran into an interesting error the other day while doing a partition switch.

Partition switch failed because : column ‘xyz’ does not have the same ANSI trimming semantics in tables ‘a’ and ‘b’

It turned out it was because the ANSI_PADDING setting was different between the two tables (well, for a single column). So first things first, what exactly is ANSI_PADDING.

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

Descriptions are nice but I like examples:

SET ANSI_PADDING ON;
CREATE TABLE Padding_ON (
is_char_NULL char(10) NULL,
is_char_NOTNULL char(10) NOT NULL,
is_varchar_NULL varchar(10) NULL,
is_varchar_NOTNULL varchar(10) NOT NULL
);
INSERT INTO Padding_ON VALUES ('aaa','aaa','aaa','aaa'),
('aaa  ','aaa  ','aaa  ','aaa  ');
SELECT DATALENGTH(is_char_NULL) AS is_char_NULL_len,
DATALENGTH(is_char_NOTNULL) AS is_char_NOTNULL_len,
DATALENGTH(is_varchar_NULL) AS is_varchar_NULL_len,
DATALENGTH(is_varchar_NOTNULL) AS is_varchar_NOTNULL_len
FROM Padding_ON;
SELECT 
is_char_NULL+'|' AS is_char_NULL,
is_char_NOTNULL+'|' AS is_char_NOTNULL,
is_varchar_NULL+'|' AS is_varchar_NULL,
is_varchar_NOTNULL+'|' AS is_varchar_NOTNULL
FROM Padding_ON;
------------------------------------------------------
SET ANSI_PADDING OFF;
CREATE TABLE Padding_OFF (
is_char_NULL char(10) NULL,
is_char_NOTNULL char(10) NOT NULL,
is_varchar_NULL varchar(10) NULL,
is_varchar_NOTNULL varchar(10) NOT NULL
);
INSERT INTO Padding_OFF VALUES ('aaa','aaa','aaa','aaa'),
('aaa  ','aaa  ','aaa  ','aaa  ');
SELECT DATALENGTH(is_char_NULL) AS is_char_NULL_len,
DATALENGTH(is_char_NOTNULL) AS is_char_NOTNULL_len,
DATALENGTH(is_varchar_NULL) AS is_varchar_NULL_len,
DATALENGTH(is_varchar_NOTNULL) AS is_varchar_NOTNULL_len
FROM Padding_OFF;
SELECT 
is_char_NULL+'|' AS is_char_NULL,
is_char_NOTNULL+'|' AS is_char_NOTNULL,
is_varchar_NULL+'|' AS is_varchar_NULL,
is_varchar_NOTNULL+'|' AS is_varchar_NOTNULL
FROM Padding_OFF;

ON is the default and is what you would expect. Trailing spaces are saved in VARCHAR and in CHAR additional spaces added to fill the entire space. When ANSI_PADDING is off then additional spaces are not saved .. unless the column is CHAR AND NOT NULL.

So there’s the first reason to not turn ANSI_PADDING off. Most people expect the ON results and the OFF results can be .. let’s just say confusing.

Next problem. The setting for the column is set when you create it. FYI You can see the setting by looking at the is_ansi_padded column in sys.columns. The setting can not be changed. This means if there is a mistake you have to create a new column with the right setting, move the data over from the old column, drop the old column and rename the new one. Sounds like a pain to me.

Oh, and just to add to the fun if you create the table, then add a new column with a different setting you will get a table with mixed settings.

SET ANSI_PADDING ON;
CREATE TABLE Padding_Mixed (Padding_On char(1));
GO
SET ANSI_PADDING OFF;
ALTER TABLE Padding_Mixed ADD Padding_Off char(1);
GO
SELECT o.name AS TableName, c.name AS ColumnName, c.is_ansi_padded
FROM sys.columns c
JOIN sys.objects o
ON c.object_id = o.object_id
WHERE o.name = 'Padding_Mixed';

Note: This affects binary/varbinary columns in the same way.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, Settings, T-SQL