upc internet polska Microsoft SQL Server 2008 Internals in Visual Basic .NET

Creation UPC-A Supplement 5 in Visual Basic .NET Microsoft SQL Server 2008 Internals

5

The rank column gives the ranking in the sort order. We can see that for the words that differ only in case, the ranking is the same. We can also see from the output that sometimes the uppercase version comes rst, and sometimes the lowercase version comes rst. This is something that is entirely arbitrary, and it s perfectly possible that you will see a different order for these pairs if you run the query yourself. If we change the collation to Latin1_General_CS_AS, we get this result:

collatedword -------------cloud CSAK cukor Oblige il IL l Opera resume RESUME r sum R SUM resumes r sum s rank -----1 2 3 4 5 6 7 8 9 10 11 12 13 14 wordno -----1 6 11 2 10 5 12 7 4 8 3 13 9 14

All entries now have a different ranking. The lowercase forms come before the uppercase forms when no other difference exists because in Windows collations, lowercase always has a lower secondary weight than uppercase. Let s now see what happens with a different language. Here s a test for the collation Hungarian_CI_AI:

collatedword -------------cloud cukor CSAK Oblige IL il Opera l R SUM RESUME r sum resume resumes r sum s rank -----1 2 3 4 5 5 6 7 8 8 8 8 9 9 wordno -----1 11 6 2 5 10 7 12 13 8 3 4 9 14

The words CSAK and l now sort after cukor and Opera. This is because in the Hungarian alphabet, CS and are letters on their own. You can also see that in this CI_AI collation, all four forms of r sum have the same rank.

In these examples, the data type for the column was nvarchar, but if you change the table to use varchar and rerun the examples, you get the same results. Character Ranges and Collations The sort order applies not only to ORDER BY clauses, but also to operators such as > and ranges in LIKE expressions. For instance, note the following code:

SELECT * FROM #words WHERE word COLLATE Latin1_General_CI_AS > 'opera'; SELECT * FROM #words WHERE word COLLATE Latin1_General_CS_AS > 'opera';

The rst SELECT lists six words, whereas the second lists seven (because in a case-sensitive collation, Opera is > opera). If you are used to character ranges from regular expressions in other languages, you may fall into the following trap when trying to select the words that start with an uppercase letter:

SELECT * FROM #words WHERE word LIKE '[A-Z]%';

But even with a case-sensitive collation, this code usually lists all 14 words. (In some languages, sorts as a separate letter after Z, so it does not fall into the speci ed range.) The range A Z is also subject to the collation rules. This also has another consequence: change cloud to aloud in the list. Using a case-sensitive collation, SELECT now returns only 13 rows. Because a sorts before A, the range A Z does not include a. As you can see, this can be a bit confusing. My advice is that you be very careful when using ranges with character data. If you need to do it, make sure that you really test the edge cases to ensure that you don t exclude any data inadvertently. Binary Collations In a binary collation, no secondary weights exist, and characters sort by their code points in the character set. So with Latin1_General_BIN2 in the previous example, we get

collatedword -------------CSAK Oblige Opera RESUME R SUM cloud cukor resume resumes r sum r sum s l IL il rank -----1 2 3 4 5 6 7 8 9 10 11 12 13 14 wordno -----6 2 7 8 13 1 11 4 9 3 14 12 5 10