T-SQL equality operator ignores trailing spaces

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!