December 5, 2011 at 10:22 am
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,CThat'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
December 5, 2011 at 10:25 am
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,CThat'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 :hehe:.
December 5, 2011 at 10:51 am
This all sounds like yet another reason to never use SQL_VARIANT columns in your database.
December 5, 2011 at 6:03 pm
Great question. Not what I would have expected....but then, many aspects of SQLVariants are not what I would expect.
Rob Schripsema
Propack, Inc.
December 6, 2011 at 12:08 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2011 at 7:58 am
Evil Kraig F (12/6/2011)
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.
What got me thinking of doing questions on variant order was a comment by someone (I think it was by Hugo in response to an earlier question, but not at all sure) that he had thought of doing a question on variant order but decided the ordering rules were too complex. I had come across variant ordering being misused for a purpose other than indexing some years back, and knew that people using SQL to create EAV systems sometimes got caught up in it. I knew that it was quite well documented in BoL, and the only things I couldn't remember about it was how to discover the locale version (the locale id was OK, but not the version) associated with a collation (couldn't remember that because I had never known it - and still don't, I haven't found it documented anywhere, haven't even looked for it much) and what happened with the newest types (date and datetime2). So I thought that maybe some questions would be amusing SQL trivia, because the rules are not really at all bizarre or complex.
As for using this stuff - well, you can probably guess from my use of the word "misuse" above that I believe that the order on SQL_VARIANT should be used only to support the construction of indexes, especially those associated with primary key and unique constraints. If people want to mess about with EAV models and worry about the order of SQL_VARIANT (beyond knowing that variant columns can be used in indexes and keys) they are, in my view, misguided (on both counts). But the rules are still fun SQL trivia.
Tom
December 7, 2011 at 8:15 am
L' Eomot Inversé (12/7/2011)
What got me thinking of doing questions on variant order was a comment by someone (I think it was by Hugo in response to an earlier question, but not at all sure) that he had thought of doing a question on variant order but decided the ordering rules were too complex.
Nope, not me. I am innocent! :hehe:
December 8, 2011 at 1:38 am
Thank you for the question
Iulian
December 11, 2011 at 5:19 am
Hugo Kornelis (12/7/2011)
L' Eomot Inversé (12/7/2011)
What got me thinking of doing questions on variant order was a comment by someone (I think it was by Hugo in response to an earlier question, but not at all sure) that he had thought of doing a question on variant order but decided the ordering rules were too complex.Nope, not me. I am innocent! :hehe:
Quite right. It wasn't you, it was Paul, in the discussion on his Collation and SQL_VARIANT question (QOTD for 1st November), see this post and my response to it.
Tom
December 15, 2011 at 12:26 am
Erudite indeed, but I don't think this is ever going to matter to me.
January 18, 2013 at 5:09 am
440692 I am just a number (12/5/2011)
Thank youA very interesting question.
Alas, I could see no other way to work out the answer, than to run the code.
+1
--
Dineshbabu
Desire to learn new things..
January 18, 2013 at 5:12 am
I don't whether i will use it in the mere future. Eventhough if i use i have to go through the explaination once again on that time.
--
Dineshbabu
Desire to learn new things..
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply