Variant Order 2

Question

Post reply

Variant Order 2

TomThomson

SSC Guru

Points: 104773
More actions
December 3, 2011 at 2:37 pm

#260860

Comments posted to this topic are about the item Variant Order 2
Tom
December 5, 2011 at 1:25 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

Thank you

A very interesting question.

Alas, I could see no other way to work out the answer, than to run the code.

Pieter de K SSC Veteran Points: 259 More actions · Answer 3

Pieter de K

SSC Veteran

Points: 259

December 5, 2011 at 2:15 am

#1416519

wrong post. my mistake...

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 4

very good question, Tom!!!

thanks!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 6

Nice question, good explanation. If i will ever have use of it, i don't know. 😉

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Pieter de K (12/5/2011)
Interesting... When I run the example on my SQL Server (Denali, CTP 3) the result is: A,D,B,E,C

That's startling. I don't have a modern (windows 7 or later) platform available to test on, but given that all the locales ids are different there should be no imaginable way for a new locale version to affect the order of these collations; the only thing I can thionk of is that some locale ids have been changed.

It would be useful if you could run the following code and post the results here, so that we can see what has happened in Denali to make this order change.

select cname CollationName, collationproperty(cname,'LCID') LCID

from (values('bosnian_latin_100_ci_as'),

('corsican_100_ci_as'),

('latin1_general_100_ci_as'),

('norwegian_100_ci_as'),

('romansh_100_ci_as')

) X(cname)

Tom

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 8

Thanks for a very interesting question.

Got it right by a wrong assumption 🙂

Figured that Latin1_General_100_CI_AS will always be the "first" and prevailing collation (and hence picked the only option having "C" as the first character).

After answering, when running and playing with the code, I found that my assumption was wrong, because if the collations included e.g. Arabic_100_CI_AS, Czech_100_CI_AS or Danish_Greenlandic_100_CI_AS, the result(s) would have been different.

You may find the following pages helpful for digging deeper into the matter:

Collation Settings--check out the SQL LCID column (since noted in hex, any value lesser than 0x0409 will be listed before Latin1_General_100_CI_AS);

Collation Names--which will list the most recent collation names (with the _100 designator in the name) along with the older equivalents (as used on the previously mentioned page; unless the collation got introduced with SQL 2008).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

L' Eomot Inversé (12/5/2011)
Pieter de K (12/5/2011)
Interesting... When I run the example on my SQL Server (Denali, CTP 3) the result is: A,D,B,E,C
That's startling. I don't have a modern (windows 7 or later) platform available to test on, but given that all the locales ids are different there should be no imaginable way for a new locale version to affect the order of these collations; the only thing I can thionk of is that some locale ids have been changed.
It would be useful if you could run the following code and post the results here, so that we can see what has happened in Denali to make this order change.
select cname CollationName, collationproperty(cname,'LCID') LCID
from (values('bosnian_latin_100_ci_as'),
('corsican_100_ci_as'),
('latin1_general_100_ci_as'),
('norwegian_100_ci_as'),
('romansh_100_ci_as')
) X(cname)

He edited out his comment.

I've run the same thing on Denali CTP3 and it returns the same results as sql 2008 R2.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 10

Good question and great explanation. I learned something new today. Thanks for submitting.

http://brittcluff.blogspot.com/

Mattrick Ten Centuries Points: 1176 More actions · Answer 11

Great question and great explanation. I can see how this could cause unexpected behavior. I definitely learned something today.

Thanks,

Matt

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Nice work on this one Tom.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 13

Thanks Tom for another great question. Another valuable lesson in an area rarely used by me in my day to day. Cheers mate!