SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / Variant Order 2 / Latest Posts

RE: Variant Order 2

Dineshbabu — Fri, 18 Jan 2013 05:12:27 GMT

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.

RE: Variant Order 2

Dineshbabu — Fri, 18 Jan 2013 05:09:05 GMT

[quote][b]440692 I am just a number (12/5/2011)[/b][hr]Thank you A very interesting question.Alas, I could see no other way to work out the answer, than to run the code.[/quote]+1

RE: Variant Order 2

charlietuna — Thu, 15 Dec 2011 00:26:37 GMT

Erudite indeed, but I don't think this is ever going to matter to me.

RE: Variant Order 2

L' Eomot Inversé — Sun, 11 Dec 2011 05:19:11 GMT

[quote][b]Hugo Kornelis (12/7/2011)[/b][hr][quote][b]L' Eomot Inversé (12/7/2011)[/b][hr]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.[/quote]Nope, not me. I am innocent! :hehe:[/quote]Quite right. It wasn't you, it was Paul, in the discussion on his Collation and SQL_VARIANT question (QOTD for 1st November), see [url=http://www.sqlservercentral.com/Forums/FindPost1198595.aspx][i]this post[/i][/url] and my [url=http://www.sqlservercentral.com/Forums/FindPost1198637.aspx][i]response[/i][/url] to it.

RE: Variant Order 2

Iulian -207023 — Thu, 08 Dec 2011 01:38:22 GMT

Thank you for the questionIulian

RE: Variant Order 2

Hugo Kornelis — Wed, 07 Dec 2011 08:15:17 GMT

[quote][b]L' Eomot Inversé (12/7/2011)[/b][hr]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.[/quote]Nope, not me. I am innocent! :hehe:

RE: Variant Order 2

L' Eomot Inversé — Wed, 07 Dec 2011 07:58:53 GMT

[quote][b]Evil Kraig F (12/6/2011)[/b][hr][quote][b]Rob Schripsema (12/5/2011)[/b][hr]Great question. Not what I would have expected....but then, many aspects of SQLVariants are not what I would expect.[/quote]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.[/quote]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.

RE: Variant Order 2

Evil Kraig F — Tue, 06 Dec 2011 12:08:00 GMT

[quote][b]Rob Schripsema (12/5/2011)[/b][hr]Great question. Not what I would have expected....but then, many aspects of SQLVariants are not what I would expect.[/quote]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.

RE: Variant Order 2

Rob Schripsema — Mon, 05 Dec 2011 18:03:57 GMT

Great question. Not what I would have expected....but then, many aspects of SQLVariants are not what I would expect.

RE: Variant Order 2

Chris Harshman — Mon, 05 Dec 2011 10:51:07 GMT

This all sounds like yet another reason to never use SQL_VARIANT columns in your database.

RE: Variant Order 2

Ninja's_RGR'us — Mon, 05 Dec 2011 10:25:02 GMT

[quote][b]L' Eomot Inversé (12/5/2011)[/b][hr][quote][b]Ninja's_RGR'us (12/5/2011)[/b][hr][quote][b]L' Eomot Inversé (12/5/2011)[/b][hr][quote][b]Pieter de K (12/5/2011)[/b][hr]Interesting... When I run the example on my SQL Server (Denali, CTP 3) the result is: A,D,B,E,C[/quote]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.[code]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)[/code][/quote]He edited out his comment.I've run the same thing on Denali CTP3 and it returns the same results as sql 2008 R2.[/quote]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.[/quote]HTH. You're still <somewhat> saine-ish :hehe:.

RE: Variant Order 2

L' Eomot Inversé — Mon, 05 Dec 2011 10:22:25 GMT

[quote][b]Ninja's_RGR'us (12/5/2011)[/b][hr][quote][b]L' Eomot Inversé (12/5/2011)[/b][hr][quote][b]Pieter de K (12/5/2011)[/b][hr]Interesting... When I run the example on my SQL Server (Denali, CTP 3) the result is: A,D,B,E,C[/quote]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.[code]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)[/code][/quote]He edited out his comment.I've run the same thing on Denali CTP3 and it returns the same results as sql 2008 R2.[/quote]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.

RE: Variant Order 2

OzYbOi d(-_-)b — Mon, 05 Dec 2011 10:15:03 GMT

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

RE: Variant Order 2

SQLRNNR — Mon, 05 Dec 2011 08:58:09 GMT

Nice work on this one Tom.

RE: Variant Order 2

Mattrick — Mon, 05 Dec 2011 06:15:28 GMT

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

RE: Variant Order 2

Britt Cluff — Mon, 05 Dec 2011 05:49:01 GMT

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

RE: Variant Order 2

Ninja's_RGR'us — Mon, 05 Dec 2011 05:23:37 GMT

[quote][b]L' Eomot Inversé (12/5/2011)[/b][hr][quote][b]Pieter de K (12/5/2011)[/b][hr]Interesting... When I run the example on my SQL Server (Denali, CTP 3) the result is: A,D,B,E,C[/quote]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.[code]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)[/code][/quote]He edited out his comment.I've run the same thing on Denali CTP3 and it returns the same results as sql 2008 R2.

RE: Variant Order 2

michael.kaufmann — Mon, 05 Dec 2011 05:20:24 GMT

Thanks for a very interesting question.Got it right by a wrong assumption :-)Figured that Latin1_General_100_CI_AS will always be the [i]"first"[/i] 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:[url=http://msdn.microsoft.com/en-us/library/ms143508.aspx]Collation Settings[/url]--check out the [b]SQL LCID[/b] column (since noted in hex, any value lesser than 0x0409 will be listed before Latin1_General_100_CI_AS);[url=http://msdn.microsoft.com/en-us/library/ms188046.aspx]Collation Names[/url]--which will list the most recent collation names (with the [b]_100[/b] designator in the name) along with the older equivalents (as used on the previously mentioned page; unless the collation got introduced with SQL 2008).

RE: Variant Order 2

L' Eomot Inversé — Mon, 05 Dec 2011 04:16:22 GMT

[quote][b]Pieter de K (12/5/2011)[/b][hr]Interesting... When I run the example on my SQL Server (Denali, CTP 3) the result is: A,D,B,E,C[/quote]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.[code]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)[/code]

RE: Variant Order 2

palotaiarpad — Mon, 05 Dec 2011 04:07:09 GMT

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

RE: Variant Order 2

Koen Verbeeck — Mon, 05 Dec 2011 03:41:29 GMT

Nice question, thanks.

RE: Variant Order 2

rfr.ferrari — Mon, 05 Dec 2011 03:00:47 GMT

very good question, Tom!!!thanks!

RE: Variant Order 2

Pieter de K — Mon, 05 Dec 2011 02:15:38 GMT

wrong post. my mistake...

RE: Variant Order 2

440692 I am just a number — Mon, 05 Dec 2011 01:47:49 GMT

Thank you A very interesting question.Alas, I could see no other way to work out the answer, than to run the code.

RE: Variant Order 2

Stewart "Arturius" Campbell — Mon, 05 Dec 2011 01:25:45 GMT

Good question, with well constructed explanation.Thanks, Tom

RE: Variant Order 2

bitbucket-25253 — Sat, 03 Dec 2011 15:00:27 GMT

Nice question

Variant Order 2

L' Eomot Inversé — Sat, 03 Dec 2011 14:37:56 GMT

Comments posted to this topic are about the item [B]Variant Order 2[/B]