Answer: Comparisons in XQuery vs T-SQL

Last week I threw out this simple little quiz. Here is the answer.

The first query will return 1 or 0 rows, depending on what the default collation in your database is. On my machine, which uses the default out-of-the-box collation "SQL_Latin1_General_CP1_CI_AS" returned a single row. The second query should always return 0 rows. All string comparisons that we do in XQuery are done in what we call the "UNICODE_CODEPOINT" collation. This is an internal-only collation which essentially does a binary comparison of the two strings. It is accent-sensitive, case-sensitive, and trailing space sensitive. Basically it is everything sensitive. When the comparison is performed outside of the scope of the XQuery statement though, we use the regular collation rules that every other string comparison uses. 

So what is the lesson? If you need collation-sensitive comparisons then you will probably have to use the XML value() method in order to retrieve your data and perform your filtering using that value. However, if you don't need collation-sensitive comparisons, then I highly recommend you stick with using the exist() method as it will usually perform better.