ANSI_Padding setting

Recently I started looking at SQL Server ANSI_Padding setting. After reading BOL and some online articles, I find myself with more questions and confusion than I started with.

I understand that I would be better off sticking to ANSI_Padding On, and I understand the different behaviors with NULL and NOT NULL columns when the setting is changed to ON or OFF.

But I cannot grasp this concept. What purpose does padding serve? Doesn't appending zeros to a binary number (or spaces to a character string) change the value?

Binary number 0x0000ee is equal to 0x00ee (at least when it is converted to decimal) But how is 0x00ee00 the same as 0x00ee?

A string 'a ' (1 space) is not the same as 'a' (no space), and yet they both become 'a ' (3 spaces) in a char(3) column. How is that even acceptable?

Same confusion also exists when values are trimmed.

BOL does seem to mention that ANSI_Padding is used to control how values are stored. So my initial guess was the actual values are not changed. But BOL does not elaborate on this point, nor do any of the articles I have found so far.

Thanks for your help.

For a varbinary column created with ANSI padding set OFF, values containing trailing zeros, such as 0x100 will have the trailing zeros truncated prior to the value being written to the column. This certainly does change the value contained in the column.

This might be useful for a system which displays values in a left-aligned fashion with right-padding zeros added at display-time, thereby reducing storage requirements. For instance, if your system knows all numbers stored in a column are 8 hex digits, left aligned, then 0x1 becomes 0x01000000 at run-time. In my opinion, the confusion this would create for future developers would far outweigh the potential space savings.

Take for example, the following:

USE tempdb;

SET ANSI_PADDING ON;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL 
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100);

SELECT *
FROM dbo.PadTest;

Result:

╔════════╗
║ v ║
╠════════╣
║ 0x0100 ║
║ 0x0001 ║
╚════════╝

SET ANSI_PADDING OFF;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL 
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100)
    , (0x001);

SELECT *
FROM dbo.PadTest;

Result:

╔════════╗
║ v ║
╠════════╣
║ 0x01 ║
║ 0x0001 ║
╚════════╝

The first table defined with ANSI padding enabled requires more storage for the first row than the second table defined with ANSI padding disabled. As you can see, this does indeed change the values stored in the table since 0x01 <> 0x0100.

SET ANSI_PADDING OFF; for varchar columns operates in a similar way, replacing the zeros with spaces. Take the following example:

SET ANSI_PADDING OFF;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL 
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt

╔═════════════╦══════════════╗
║ varchar(10) ║ char(10) ║
╠═════════════╬══════════════╣
║ a| ║ a | ║
║ a| ║ a | ║
╚═════════════╩══════════════╝

I've added the pipe symbol to the output to show where padding exists and doesn't exist.

SET ANSI_PADDING ON;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL 
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt

╔═════════════╦══════════════╗
║ varchar(10) ║ char(10) ║
╠═════════════╬══════════════╣
║ a| ║ a | ║
║ a | ║ a | ║
╚═════════════╩══════════════╝

The char(10) columns operate "correctly" for both variations of the SET ANSI_PADDING statement - the varchar(10) columns are a bit problematic with SET ANSI_PADDING OFF; since it's removing the spaces from the value. This is why in Microsoft's ANSI_PADDING documentation, it states:


We recommend that ANSI_PADDING always be set to ON.

StackExchange Database Administrators Q#180031, answer score: 3