Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Variant Order 2 Expand / Collapse
Author
Message
Posted Monday, December 5, 2011 5:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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.
Post #1216217
Posted Monday, December 5, 2011 5:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good question and great explanation. I learned something new today. Thanks for submitting.

http://brittcluff.blogspot.com/
Post #1216234
Posted Monday, December 5, 2011 6:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:43 PM
Points: 581, Visits: 732
Great question and great explanation. I can see how this could cause unexpected behavior. I definitely learned something today.

Thanks,

Matt
Post #1216247
Posted Monday, December 5, 2011 8:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Nice work on this one Tom.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1216371
Posted Monday, December 5, 2011 10:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
Thanks Tom for another great question. Another valuable lesson in an area rarely used by me in my day to day. Cheers mate!
Post #1216484
Posted Monday, December 5, 2011 10:22 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:40 PM
Points: 8,743, Visits: 9,292
Ninja's_RGR'us (12/5/2011)
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.

Thanks for the good news, Remi; I was quite wondering what on earth could be going on, and it's nice to know that it was nothing.


Tom
Post #1216491
Posted Monday, December 5, 2011 10:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
L' Eomot Inversé (12/5/2011)
Ninja's_RGR'us (12/5/2011)
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.

Thanks for the good news, Remi; I was quite wondering what on earth could be going on, and it's nice to know that it was nothing.


HTH. You're still <somewhat> saine-ish .
Post #1216492
Posted Monday, December 5, 2011 10:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:24 AM
Points: 1,867, Visits: 2,020
This all sounds like yet another reason to never use SQL_VARIANT columns in your database.
Post #1216514
Posted Monday, December 5, 2011 6:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:24 AM
Points: 1,974, Visits: 10,962
Great question. Not what I would have expected....but then, many aspects of SQLVariants are not what I would expect.



Rob Schripsema
Accelitec, Inc
Post #1216747
Posted Tuesday, December 6, 2011 12:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 7:18 PM
Points: 6,250, Visits: 7,409
Rob Schripsema (12/5/2011)
Great question. Not what I would have expected....but then, many aspects of SQLVariants are not what I would expect.


Agreed, it's like opening Pandora's Box. I'm not even sure what led Tom to finding this nugget, nevermind how I'd go about finding the full answer if he hadn't spoon-fed me what was going on in a reasonable amount of time.

Variant can burn from everything I've been seeing on the complexity of its rulesets.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1217347
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse