Systems Engineering and RDBMS

ANSI_PADDING and trailing spaces in SQL Server

Posted by decipherinfosys on February 15, 2007

In SQL Server, ANSI_PADDING setting controls the “storage” part of the trailing space and that is done at the time of the creation of the tables.  SQL Server follows the ANSI standard on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings ‘xyz’ and ‘xyz ‘ to be equivalent for most comparison operations.

The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons. And the setting is always on by default.

Example:

CREATE TABLE DEMO1_TEST (COL1 VARCHAR(10))
GO
–W/O SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1′);
–WITH SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1 ‘);
GO
SELECT COL1, DATALENGTH (COL1) AS LENGTH
FROM DEMO1_TEST
GO
COL1       LENGTH
———- ———–
DEMO1     5
DEMO1     6

SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1 ‘
GO
SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1′
GO

CNT
———–
2

CNT
———–
2

NOTE: If the table was created with ANSI_PADDING set to OFF, then SQL Server will automatically remove the space…however, as I mentioned before, it follows the ANSI behavior and that does not effect comparisons:

DROP TABLE DEMO1_TEST
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE DEMO1_TEST (COL1 VARCHAR(10))
GO
–W/O SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1′);
–WITH SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1 ‘);
GO
SELECT COL1, DATALENGTH (COL1) AS LENGTH
FROM DEMO1_TEST
GO

COL1       LENGTH
———- ———–
DEMO1     5
DEMO1     5                => NOTE: It removed the trailing space because you told it to do so.

SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1 ‘
GO
SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1′
GO

CNT
———–
2

CNT
———–
2

Trailing spaces should never be allowed through the application.  All the RDBMS do not behave the same way and the onus thus lies upon the application to never allow trailing spaces.  It has other implications as well at the application tier.  Spaces should only be allowed if they are part of the data itself like ‘John Doe’s Wonderful Show’ where the spaces mean something.

About these ads

2 Responses to “ANSI_PADDING and trailing spaces in SQL Server”

  1. [...] gotcha that we would like to point out is with the comparison of non-unicode string with trailing spaces with a unicode [...]

  2. [...] Do note that if you enter in an empty string of more than zero length, in SQL Server, the result would be the same.  ANSI_PADDING does effect the storage but the comparison rules remain the same.  You can read more on that in one of our previous blog posts here. [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: