December 12, 2011 at 10:22 am
jts_2003 (12/12/2011)
Can anyone explain the answer, as I didn't understand the explanation at all 🙂
I guess the thing to do is to read teh BoL page referenced, plus read the explanation for Paul's 1Nov QotD, the explanation for my 30 Nov QotD, and the explanation for my 5 Dec QotD before attempting to understand this one. Ordering of SQL_VARIANT is pretty complex, and it seems that a lot of people find the very brief explanation in BoL unhelpful (hence my rather verbose explanation for the 5 Dec QotD).
Tom
December 12, 2011 at 11:35 am
So nearly two-thirds of respondants so far are smarter than I. They wouldn't have just run the code and clicked the corresponding option now, would they?
Anyway, I ended up guessing wrong, partially based on a table of SQL Collation Names with sort order values found here: http://msdn.microsoft.com/en-US/library/ms180175(v=SQL.105).aspx
Despite Tom's explanations, which so far make sense to me, I don't understand how this table would have value if it doesn't bear out the actual order. Perhaps he or one of the 63% who got the correct answer could clarify this.
December 12, 2011 at 1:11 pm
Thanks for taking the time to reply Tom...
December 12, 2011 at 1:20 pm
john.arnott (12/12/2011)
So nearly two-thirds of respondants so far are smarter than I. They wouldn't have just run the code and clicked the corresponding option now, would they?
I suspect some - perhaps most - did just that. It's very difficult to do any other way if you don't just happen to know that _cs is lower than _ci, _as is lower than _ai, _ks is lower than blank, _ws is lower than blank, they affect the order in the order I've listed them here, and that BIN comes last, and then remember the bizarre positioning of BIN2. I included a list of ComparisonStyles and their order in the explanation without any reference in the explanation because I haven't a clue where I got in from (I verified that it still worked when I found my rather old (although I must have updated them when 2005 came along) notes on it).
Anyway, I ended up guessing wrong, partially based on a table of SQL Collation Names with sort order values found here: http://msdn.microsoft.com/en-US/library/ms180175(v=SQL.105).aspx
Despite Tom's explanations, which so far make sense to me, I don't understand how this table would have value if it doesn't bear out the actual order. Perhaps he or one of the 63% who got the correct answer could clarify this.
That's actually quite straightforward. The "sort order id" of this column is the "sort id" of page about SQL_VARIANT; that means that if two collations have the same Locale ID, the same Locale Version, and the same ComparisonStyle then (and only then) is this "sort order id" used to determine the order - and for the collations in the question, it's already been determined by Locale ID and version and ComparisonStyle, so no account is taken of the "order id". (I'm using both names, so as to match BoL on this, but using quote marks to make it obvious that the naming is adrift).
I should probably have included the above in the explanation, but I didn't even think of it at the time. I suppose I can point out in mitigation that there are a couple of things about that page and the table it contains that might have acted as warning flags. The table on that page lists only 200 collations out of the 2397 that my copy of SQL 2008 R2 has, so it isn't going to be the whole story, not even when you allow for the fact that sort order id 80 may bring that total up to 250 or maybe more by allowing many different collations that use binary order and code page 1250. The fact that this page shows that collations for different locales can have the same sort order id demonstrates that this sort order id can't be the whole of the ordering of collations for sql_variant, because the locale id is the most significant of the attributes that determine that order. The table only gives a sort order id for three of the collations used in the question, the other two are missing.
Only a small portion of the windows collations introduced for sql use in or before SQL 2000 appear to have been given sort ids. As I understand it, if two windows collations have the same locale id, locale version (I believe implies that they have the same collation version and I know it doesn't follow from their having the same collation version), and the same ComparisonStyle they will also have the same "sort order id" and hence will compare equal as collations on SQL_VARIANT values. But I could be wrong - I can't find any documentation to back that last statement up, it's just something IO seem to remember out of the misty past, and not having found any counterexamples is no guarantee (especially as I haven't looked very hard).
Tom
December 12, 2011 at 3:57 pm
It's official. I hate Variant.
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 13, 2011 at 12:41 am
L' Eomot Inversé (12/12/2011)
What's the most important use of SQL_VARIANT order? Well, in my experience its main use is in generating T-SQL trivia questions for use as QotD - we've had 5 since 1st November (1 from Paul, 1 from bitbucket, and 3 from me).
True, true 🙂
Best Regards,
Chris Büttner
December 13, 2011 at 12:48 am
john.arnott (12/12/2011)
So nearly two-thirds of respondants so far are smarter than I. They wouldn't have just run the code and clicked the corresponding option now, would they?
Well, you can assume that noone knew the answer before running the code.
I for myself decided pretty quickly that I do not know the answer (by heart), and that learning the correct answer from BOL or other sources will not advance my SQL skills significantly. So I decided to shortcut by running the code directly.
And no, I usually dont short-cut, only in such special occasions.
Hope this does not disappoint you.
Best Regards,
Chris Büttner
December 13, 2011 at 5:26 am
good question tom!!!!
thanks!!!!
December 13, 2011 at 9:10 am
Christian Buettner-167247 (12/13/2011)
john.arnott (12/12/2011)
So nearly two-thirds of respondants so far are smarter than I. They wouldn't have just run the code and clicked the corresponding option now, would they?Well, you can assume that noone knew the answer before running the code.
I for myself decided pretty quickly that I do not know the answer (by heart), and that learning the correct answer from BOL or other sources will not advance my SQL skills significantly. So I decided to shortcut by running the code directly.
And no, I usually dont short-cut, only in such special occasions.
Hope this does not disappoint you.
Not at all. Rather, I'm kicking myself for spending so much time trying to track down the answer when I knew that Tom would have it for me.
December 13, 2011 at 2:16 pm
WHAT?!?!?? you mean that people run the code then take credit for the answer? I guess I don't have to feel guilty when I google the QOTD any more. 😛
December 13, 2011 at 7:58 pm
Uripedes Pants (12/13/2011)
😛
I'm going completely off topic:
With someone using that nickname (and this late at night, with perhaps too many jars downed in the course a minor celebration) I just can't resist asking G?at? ? ????p?d?? as?µa???? Using Greek when asking someone using Euripides as a nickname seems appropriate, even if my Greek is that bad.
And that led to an increase of my distrust in modern machine translators (they seem worse than what Siemens had about 20 years ago).
My Greek is extremely limited, not quite totally nonexistent but extremely close to that (closer than you probably think - anyway, I hadn't a clue what the Greek for "pants" - in any of its meanings - might be) so I tried an automatic translation first; it insisted on using s?ß?a?? or pa?te???? and the second option of those options made me suspicious; and a quick dictionary check indicated that the first option was no better. Although I trust Google translate not one jot, I tried that next - and got G?at? e??a? ?t? ? ????p?d?? pa?te????, which is absolute pants; then I tried asking google translate to translate "Pourquoi Euripide halète-t-il ?" to Greek, and got those pa?te???? again (in "G?at? ? ????p?d?? pa?te???? e?e?", which to me suggests that it does some of its French to Greek by translating French to English and then English to Greek, because as well as "pants" the Google English translation for that phrase has a spurious "there" in it, which is maybe where that spurious "e?e?" in the Greek comes from).
Since I am totally unable to conjugate Greek verbs and the machine translators failed me and my beginners modern greek text book is more than a thousand miles away (and it would probably take me several hours to find it even if I was there - it's years since I decided ancient and modern Greek were two languages too many) I ended up with the above words as my attempt at asking the obvious question, but I guess anyone calling himself Euripedes should be able to make sense out of my utterly awful Greek.
I have a niggling suspicion that the machine translations - even Google's - conformed to the intent of the nickname, unless perhaps that particular metaphor is not used in Greek. But what I asked them to translate was using "pant" as a verb, and translating something which syntactically has to be a verb as if it were an unrelated noun was a gross failure of the machine translators (and Google's screw-up translating from French was a total failure, since there's no way "halète" can bear that meaning).
Tom
December 14, 2011 at 1:10 am
(still off-topic)
Tom, I fooled around a bit with both Bing Translator and Google Translate, and I am convinced that they both suffer from the same bug, introduced by a combination of using English as an intermediate language and not looking at words in their grammatical context.
I tried to translate the sentences "Euripides hijgt" and "Waarom hijgt Euripides?" to Greek, and got the pa?te???? translation for both variations and on both sites.
I then tried "Waarom is Euripides aan het hijgen?" (Dutch for "Why is Euripides panting?"). Bing Translator gave me the utterly unhelpful "G?at? e??a? ????p?d?, ??a ?a t? panting;", but Google Translate seems to do better here: "G?at? e??a? ? ????p?d?? ?a ?a?a?????;". Translating the latter back to Dutch on Bing Translator was truly hilarious - the result was "Waarom is Euripides aan bladerdeeg?" (Why is Euripides on puff pastry?). My guess is that the translation was again indirect, that ?a?a????? was translated as puff rather than pant, and that the word puff was utterly misunderstood for the second phase (English to Dutch) of the translation.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply