SQL Server Performance Forum – Threads Archive

How does SQL order

A couple of us were sitting around pondering over an issue we saw today that I don’t have a good explanation for so I thought I’d see if anyone else knows. We have a table with a varchar field in it. If you select from this table with an order by the varchar field, why would a value of ‘{‘ show up before a value of ‘a’ instead of after a value of ‘z’? I would assume that when SQL orders varchar it would use the ascii values but apparently that is not the case. What does sql use to determine order in a varchar situation? result
——-
{
a
z

Experience tells me the ordering starts with non-numeric non-alphabetical characters (in numerical order of the ASCII value), then numeric characters (ordered like alphabetical characters, so 10 comes before 2) and finally alphabetical characters. For the alphabetical characters, the current collation setting decides case sensitivity and local variations.

Somewhere deep inside BOL when digging for collations you’ll find a hint. I know it is there, but can’t find it now. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

When you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. I would suggest to refer books online for COLLATION topic for more explanation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>