5

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.

2 Answers 2

3

First - here's a recent article that shows how things work pretty well.

There's also this article from Microsoft, explaining how SQL Server handles comparisons when strings have trailing spaces.

Why have padding? For NOT NULL columns where all the data will be very close to the same length, padding everything to (say) 10 characters may result in less space consumed than adding the overhead of making the column variable length. I think that making the column NULLable requires the same overhead as making it variable length, so including the padding becomes optional.

When you mention that BOL is talking about how the values are stored - yes, this does change the value. If you store 'a' (no space) and 'a ' (one space) in a char(3) NOT NULL column, they'll both be retrieved as 'a' (two spaces). IN a char(3) NULL column, they'll be retrieved as 'a' or 'a ', depending on the ANSI_PADDING setting when the table (or column) was created.

Why does the loss or retention of trailing spaces seem so unimportant?

Basically, when comparing two strings, SQL pads the shorter string out with spaces until they're the same length. So:

SELECT CASE WHEN 'a' = 'a ' THEN 'TRUE' ELSE 'FALSE' END as String_Comp;

returns TRUE.

Also - binary() and varbinary() should maybe not be thought of as being simply binary representations of numbers (which, of course, they are), but as binary strings. In comparisons of binary values as binaries, SQL does not pay attention to trailing zeroes.

An example: 0X00EE00 is the decimal number 60928. 0X00EE is the decimal number 238.

SELECT CASE WHEN CAST(0x00EE00 as int) = CAST(0x00EE as int)
         THEN 'TRUE'
         ELSE 'FALSE'
       END as Binary_Number_Comp;

returns FALSE (60928 <> 238). However:

SELECT CASE WHEN CAST(0x00EE00 as binary(10)) = CAST(0x00EE as varbinary(6))
         THEN 'TRUE'
         ELSE 'FALSE'
       END as Binary_String_Comp;

returns TRUE; as noted by Max Vernon, the trailing zeroes are acting like a NULL terminator in a string, and are not included in the comparison.

Interestingly:

SELECT CASE WHEN 0x00EE00 = 0x00EE THEN 'TRUE' ELSE 'FALSE' END as Binary_Value_Comp;

returns TRUE, so by default this is comparing the values as binary strings, not binary numbers.

Per the docs above, a LIKE comparison is the only way to differentiate between string values based on trailing zeroes.

SELECT CASE WHEN 'a' LIKE 'a ' THEN 'TRUE' ELSE 'FALSE' END as LIKE_String_Comp;

returns FALSE.

SO, in summary, unless you really work at it, SQL Server ignores trailing spaces regardless of the ANSI_PADDING settings, which is why trimming them or not is treated so cavalierly.

And, by the way, here's a quick example of the impact of ANSI_PADDING

Query:

SET ANSI_PADDING ON;

IF (OBJECT_ID('tempdb..#test') IS NOT NULL) DROP TABLE #test;

CREATE TABLE #test
     ( my_char CHAR(10) NOT NULL
      ,my_varchar VARCHAR(10) NOT NULL
      ,my_char_NULL CHAR(10) NULL
      ,my_binary BINARY(6) NOT NULL
      ,my_varbinary VARBINARY(6) NOT NULL
      ,my_binary_NULL BINARY(6) NULL
     );

INSERT INTO #test
VALUES ('ABC   ','ABC   ','ABC   ',0xABC000,0xABC000,0xABC000);

PRINT 'ANSI_PADDING ON:';

SELECT '|' + my_char + '|' as my_char
      ,'|' + my_varchar + '|' as my_varchar
      ,'|' + my_char_NULL + '|' as my_char_NULL
      ,my_binary
      ,my_varbinary
      ,my_binary_NULL
  FROM #test;

SET ANSI_PADDING OFF;

IF (OBJECT_ID('tempdb..#test2') IS NOT NULL) DROP TABLE #test2;

CREATE TABLE #test2
     ( my_char CHAR(10) NOT NULL
      ,my_varchar VARCHAR(10) NOT NULL
      ,my_char_NULL CHAR(10) NULL
      ,my_binary BINARY(6) NOT NULL
      ,my_varbinary VARBINARY(6) NOT NULL
      ,my_binary_NULL BINARY(6) NULL
     );

INSERT INTO #test2
VALUES ('ABC   ','ABC   ','ABC   ',0xABC000,0xABC000,0xABC000);

PRINT 'ANSI_PADDING OFF:';

SELECT '|' + my_char + '|' as my_char
      ,'|' + my_varchar + '|' as my_varchar
      ,'|' + my_char_NULL + '|' as my_char_NULL
      ,my_binary
      ,my_varbinary
      ,my_binary_NULL
  FROM #test2;

Results:

ANSI_PADDING ON:
my_char      my_varchar   my_char_NULL my_binary      my_varbinary   my_binary_NULL
------------ ------------ ------------ -------------- -------------- --------------
|ABC       | |ABC   |     |ABC       | 0xABC000000000 0xABC000       0xABC000000000


ANSI_PADDING OFF:
my_char      my_varchar   my_char_NULL my_binary      my_varbinary   my_binary_NULL
------------ ------------ ------------ -------------- -------------- --------------
|ABC       | |ABC|        |ABC|        0xABC000000000 0xABC0         0xABC0



NOTE: As Max Vernon pointed out in a comment on his answer, my notes above are all about how SQL Server handles things. Equality of values in whatever application may be retrieving data from SQL Server may very well behave entirely different.

0
3

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.

1
  • With nullable columns, you could make the argument the behaviour with ANSI_PADDING ON is less consistent dbfiddle.uk/KiR3JF_6
    – Paul White
    Dec 17, 2022 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.