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: When executed against the Northwind database included with SQL Server they both return the same row, which has no trailing spaces after its TerritoryDescription. 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: 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!