A while ago I was quite surprised to see that the following query returns 1 instead of 0:

SELECT COUNT(*) WHERE N'Tim' = N'Tim '; -- notice the trailing space

Apparently this is just standard behaviour. Here is an extract from sql1992.txt (Section 8.2 Paragraph 3):