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!