Today I discovered something new about SQL Server while debugging an application. T-SQL’s equality operator ignores any trailing spaces when comparing strings. Thus, these two statements are functionally equivalent:
SELECT * FROM Territories WHERE TerritoryDescription = 'Savannah' SELECT * FROM Territories WHERE TerritoryDescription = 'Savannah '
When executed against the Northwind database included with SQL Server they both return the same row, which has no trailing spaces after its TerritoryDescription.
TerritoryID TerritoryDescription RegionID -------------------- -------------------------------------------------- ----------- 31406 Savannah 4 (1 row(s) affected)
This behaviour isn’t immediately obvious from the offset, and isn’t mentioned on the MSDN entry.
To avoid this problem, you should use LIKE instead:
SELECT * FROM Territories WHERE TerritoryDescription LIKE 'Savannah '
When comparing strings with LIKE all characters are significant, including trailing spaces.
Update: a co-worker discovered yesterday that using LIKE in T-SQL JOINs doesn’t use indices in the same way that the equals operator does. This can have a significant impact on performance. Be warned!
December 28, 2007
