SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Variant Order 2


Variant Order 2

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14166 Visits: 12197
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

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10799 Visits: 11966
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


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Iulian -207023
Iulian -207023
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 1233
Thank you for the question

Iulian
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14166 Visits: 12197
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

charlietuna
charlietuna
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 106
Erudite indeed, but I don't think this is ever going to matter to me.
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1180 Visits: 569
440692 I am just a number (12/5/2011)
Thank you
A 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..
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1180 Visits: 569
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..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search