There is all the time confusion in the developer’s daily job how exactly is SQL Server handling padding spaces before and after strings in comparison and data persistence. Time to make it more clear now.
The few most important things to remember are:
- SQL Server follows the ANSI/ISO SQL-92 specification 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 WHERE and HAVING clause predicates and other Transact-SQL string comparisons.
- The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs.
- 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.
There is a simple script to check how it works:
;WITH s AS
(
SELECT *, DATALENGTH(String) DataLen FROM (VALUES ('abc'), ('abc ')) a(String)
)
SELECT 'EqualWithSpace' Test, * FROM [s] WHERE String = 'abc ' UNION ALL
SELECT 'EqualNoSpace ', * FROM s WHERE String = 'abc' UNION ALL
SELECT 'GTWithSpace ', * FROM s WHERE String > 'ab ' UNION ALL
SELECT 'GTNoSpace ', * FROM s WHERE String > 'ab' UNION ALL
SELECT 'LTWithSpace ', * FROM s WHERE String < 'abd ' UNION ALL
SELECT 'LTNoSpace ', * FROM s WHERE String < 'abd' UNION ALL
SELECT 'LikeWithSpace ', * FROM s WHERE String LIKE 'abc %' UNION ALL
SELECT 'LikeNoSpace ', * FROM s WHERE String LIKE 'abc%'
GO
You can see that SQL Server handles strings ‘abc’ and ‘abc ‘ as to be the same. The only exception is LIKE ‘abc %’ where space means search condition that any character can be at the position in searched expression.
How the use of SET ANSI_PADDING ON will affect our script? There will be no difference because this setting is affecting data at the time of writing but not reading. To check this we should modify our script and save data to a temporary table first:
SET ANSI_PADDING OFF
GO
CREATE TABLE #s (String varchar(10))
GO
INSERT INTO #s VALUES ('abc ')
INSERT INTO #s VALUES ('abc')
GO
SELECT 'EqualWithSpace' Test, *, DATALENGTH(String) DataLen FROM #s WHERE String = 'abc ' UNION ALL
SELECT 'EqualNoSpace ', *, DATALENGTH(String) FROM #s WHERE String = 'abc' UNION ALL
SELECT 'GTWithSpace ', *, DATALENGTH(String) FROM #s WHERE String > 'ab ' UNION ALL
SELECT 'GTNoSpace ', *, DATALENGTH(String) FROM #s WHERE String > 'ab' UNION ALL
SELECT 'LTWithSpace ', *, DATALENGTH(String) FROM #s WHERE String < 'abd ' UNION ALL
SELECT 'LTNoSpace ', *, DATALENGTH(String) FROM #s WHERE String < 'abd' UNION ALL
SELECT 'LikeWithSpace ', *, DATALENGTH(String) FROM #s WHERE String LIKE 'abc %' UNION ALL
SELECT 'LikeNoSpace ', *, DATALENGTH(String) FROM #s WHERE String LIKE 'abc%'
GO
The core change is that our DataLen is now 3 everywhere and this means that SQL Server stored our data without padding spaces. We see that all rows for LIKE are gone In our result set because compare conditions don’t match.
You should remember two other important things:
- SET ANSI_PADDING is a deprecated feature for Azure SQL and will be deprecated for SQL Server soon
- SET ANSI_PADDING should be set to ON always