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 Wednesday, December 7, 2011 7:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 8,503, Visits: 8,991
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
Post #1217928
Posted Wednesday, December 7, 2011 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:53 PM
Points: 5,890, Visits: 8,148
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!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1217944
Posted Thursday, December 8, 2011 1:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 887, Visits: 810
Thank you for the question

Iulian
Post #1218428
Posted Sunday, December 11, 2011 5:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 8,503, Visits: 8,991
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!

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
Post #1219910
Posted Thursday, December 15, 2011 12:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:24 AM
Points: 310, Visits: 75
Erudite indeed, but I don't think this is ever going to matter to me.
Post #1222200
Posted Friday, January 18, 2013 5:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:36 AM
Points: 926, Visits: 556
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..
Post #1408833
Posted Friday, January 18, 2013 5:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:36 AM
Points: 926, Visits: 556
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..
Post #1408835
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse