Variant Order 3

  • Comments posted to this topic are about the item Variant Order 3

    Tom

  • Hmmm Not wanting to give away the reasoning utilized to determine the correct answer I am posting this comment - so as to receive automatic notifications of additions to the discussion of your QOD, which is some regards very similar to the QOD on SQL VARIANT that I had published on 10 Nov. 2011

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (12/10/2011)


    Hmmm Not wanting to give away the reasoning utilized to determine the correct answer I am posting this comment - so as to receive automatic notifications of additions to the discussion of your QOD, which is some regards very similar to the QOD on SQL VARIANT that I had published on 10 Nov. 2011

    Yes it is similar in some regards, but nothing like as similar as my Variant Order 1 question on 30 Nov. In fact I was quite worried when your question came out on 10 Nov, because of the very great similarity between that an the one I had submitted a few days before.

    My series of three questions on variant order had its origins in the discussion of Paul's Collation and SQL_VARIANT QotD for 1 Nov, see this post.

    BTW, something has clearly gone strange with your 10 Nov question as the explanation has different column names, different values, and different number of rows from the question and answer. I don't think it can have been like that when it was first published, or I would have commented then (and even if somehow I hadn't noticed, I'm sure someone else would have commented on the discrepancies). Maybe you could give Steve a revised explanation (or maybe he wouldn't want one this long after the event)?

    Tom

  • Thanks Tom.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Difficult one, and that for a Monday.

    Thanks Tom for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Surprisingly difficult, especially considering the % of correct answers!

    Thanks for the question and the point 😀


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you for the question

    Iulian

  • Can anyone explain the answer, as I didn't understand the explanation at all 🙂

  • Tough question, thanks for submitting.

    http://brittcluff.blogspot.com/

  • I liked the first question in the series, but I did not really need the follow-ups.

    The first question already taught me to never ever consider order by SQL_VARIANT columns.

    Maybe I have missed it, but is there any plausible reason to order by a SQL_VARIANT column, given the unintuitive sorting semantics? What problem can I solve with that order?

    Best Regards,

    Chris Büttner

  • Christian Buettner-167247 (12/12/2011)


    Maybe I have missed it, but is there any plausible reason to order by a SQL_VARIANT column, given the unintuitive sorting semantics? What problem can I solve with that order?

    A well-defined sort order for SQL_VARIANT is a requirement to be able to build indexes on it.

    I don't think there is any other reason to use comparisons (other than equal to or unequal to) on SQL_VARIANT data.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • There is something that I don't understand about this one.

    The article that is referenced in the solution says:

    "When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are COMPARED AS INTEGER VALUES, and in the order listed"... (capitalization is mine, not in BOL)

    I looked at the ComparisionStyle property for the collations used in the question (using SELECT COLLATIONPROPERTY('[collation_name], 'ComparisonStyle')) and this is what I got

    - latin1_general_bin = 0

    - latin1_general_cs_as = 196608

    - latin1_general_ci_as = 196609

    - latin1_general_cs_ai = 196610

    - latin1_general_ci_ai = 196611

    Based on these results... I expected the order to be E, C, A, D, B...

    I am obviously missing something, what is that?

    Thanks!

  • Christian Buettner-167247 (12/12/2011)


    I liked the first question in the series, but I did not really need the follow-ups.

    The first question already taught me to never ever consider order by SQL_VARIANT columns.

    Maybe I have missed it, but is there any plausible reason to order by a SQL_VARIANT column, given the unintuitive sorting semantics? What problem can I solve with that order?

    I think the only reason for having an order on the SQL_VARIANT data type is to allow it to occur in indexes (including indexes required to support UNIQUE and PRIMARY KEY constraints). I don't think it's generally useful to know much about the order, and I don't imagine that knowing the order could help in writing more efficient queries. Obviously knowing the order is useful to those who have to write a data engine that supports it, so it's of interest to those who write data engines. I don't think it's of much interest to anyone else, not event to practitioners or EAV models (which are where most SQL variants turn up, of course: the alternative of using NVARCHAR(4000) to contain text which can be read as SQL speciying the underlying type, the collation if relevant, and the base value is even more dreadful than using SQL_VARIANT; of course there's little point in using an RDBMS if one's aim is to provide an EAV model, but....).

    I have seen a case where the primary key of a table was an SQL_VARIANT column; it's usefulness was not as a determiner of row proximity in physical store or as an efficient way of looking at small key-ranges but as a something that was unique. For the thing to be used as a primary key, there had to be an order so that the index supporting the primary key could be constructed. There was no combination of other columns in the table that would be unique on its own, so the table had to include that column in the primary key. It wasn't my design (I'd never have done it that way) but it seemed reasonable. So although it's rare, using the order happens - but as far as I know, knowing what the order is as opposed to knowing that there is an order is useful only to those who have to implement the data engine.

    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).

    Tom

  • Tks Tom.

  • msurasky-905715 (12/12/2011)


    There is something that I don't understand about this one.

    The article that is referenced in the solution says:

    "When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are COMPARED AS INTEGER VALUES, and in the order listed"... (capitalization is mine, not in BOL)

    I looked at the ComparisionStyle property for the collations used in the question (using SELECT COLLATIONPROPERTY('[collation_name], 'ComparisonStyle')) and this is what I got

    - latin1_general_bin = 0

    - latin1_general_cs_as = 196608

    - latin1_general_ci_as = 196609

    - latin1_general_cs_ai = 196610

    - latin1_general_ci_ai = 196611

    Based on these results... I expected the order to be E, C, A, D, B...

    I am obviously missing something, what is that?

    Thanks!

    I should have included a warning about that one in my explanation for this question - the problem is that the comparison style returned by COLLATIONPROPERTY for a collation is not the Comparison Style used in comparing collations to order SQL_VARIANT values; in particular, it delivers 0 both for _BIN and for _BIN2 for all locales (despite these being generally quite distinct comparison styles), whereas variant puts these two just about at opposite ends of the scale (at second and last out of 18). Some of the other numbers it returns also fail to match up with the style number used in variant sorting (eg it returns 0 for _CS_AS_KS{_WS too). And I don't know whether this is a bug in the COLLATIONPROPERTY function; but as returning the same value - 0 - for two different styles is actually documented, I guess it's not a bug unless the documentation has a partially matching bug. Most probably it is just another failure of BoL to tell us what is actually going on. I also don't know what the integer comparison styles used by the SQL_VARIANT order are; just that their order matches the list I provided.

    The Collation version returned by COLLATIONPROPERTY also differs from the LCID version used in sorting variants, as noted in the explanation of the previous question in this series, but that of course is fair enough - it's a collation version, not a locale version. The issue you have hit on is rather nastier.

    Incidentally, sort ID is something that old-fashioned SQL collations (collation name beginning SQL_) had, and can be derived somehow for Windows collations (Windows collations may once have had it; I don't think modern ones do) to help people understand how to maintain compatability with databases which used older collations. I don't think it comes into the ordering of windows collations used in SQL_VARIANT at all (but I may be wrong - the documentation says it does, as you point out - it says sort ID is compared, not sort ID is compared if it exists).

    Tom

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply