Turning ANSI_PADDING off, and why you shouldn’t

4

June 15, 2017 by Kenneth Fisher

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.

4 thoughts on “Turning ANSI_PADDING off, and why you shouldn’t

  1. […] Kenneth Fisher explains what the ANSI PADDING setting does: […]

  2. henrikstaunpoulsen says:

    If you are really keen to ensure that Ansi Padding is off, you get a check added to sp_Blitz, see https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

  3. […] If ansi_padding is off then the trigger will fail and so will the change. Of course I’m not sure I consider that a […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: