Comparing with SQL_VARIANT

  • Comments posted to this topic are about the item Comparing with SQL_VARIANT

    God is real, unless declared integer.

  • This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange - Friday, July 14, 2017 7:36 AM

    This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    Agreed wholeheartedly.
    it is a good concept for a question,  though, enjoyed working it out..

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Stewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AM

    Sean Lange - Friday, July 14, 2017 7:36 AM

    This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    Agreed wholeheartedly.
    it is a good concept for a question,  though, enjoyed working it out..

    If you look at it as a training exercise (part of the mission of QOD) where you actually go look up things (like what an SQLVARIANT is) rather than as a question, I think it's rather good.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Friday, July 14, 2017 2:01 PM

    Stewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AM

    Sean Lange - Friday, July 14, 2017 7:36 AM

    This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    Agreed wholeheartedly.
    it is a good concept for a question,  though, enjoyed working it out..

    If you look at it as a training exercise (part of the mission of QOD) where you actually go look up things (like what an SQLVARIANT is) rather than as a question, I think it's rather good.

    From the learning perspective it is excellent. Just so many lines to parse mentally it makes it super tempting to just throw up your hands after the first 4 or 5.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange - Friday, July 14, 2017 2:18 PM

    Jeff Moden - Friday, July 14, 2017 2:01 PM

    Stewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AM

    Sean Lange - Friday, July 14, 2017 7:36 AM

    This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    Agreed wholeheartedly.
    it is a good concept for a question,  though, enjoyed working it out..

    If you look at it as a training exercise (part of the mission of QOD) where you actually go look up things (like what an SQLVARIANT is) rather than as a question, I think it's rather good.

    From the learning perspective it is excellent. Just so many lines to parse mentally it makes it super tempting to just throw up your hands after the first 4 or 5.

    And now you know why I don't submit to most people's ideas of interview tests/questions. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Friday, July 14, 2017 2:28 PM

    Sean Lange - Friday, July 14, 2017 2:18 PM

    Jeff Moden - Friday, July 14, 2017 2:01 PM

    Stewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AM

    Sean Lange - Friday, July 14, 2017 7:36 AM

    This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    Agreed wholeheartedly.
    it is a good concept for a question,  though, enjoyed working it out..

    If you look at it as a training exercise (part of the mission of QOD) where you actually go look up things (like what an SQLVARIANT is) rather than as a question, I think it's rather good.

    From the learning perspective it is excellent. Just so many lines to parse mentally it makes it super tempting to just throw up your hands after the first 4 or 5.

    And now you know why I don't submit to most people's ideas of interview tests/questions. 😉

    That and most people can't answer the most basic of interview questions....how to get the current date in t-sql.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I thought it was a good question that really made me think through each possibility.  It took a while, but so does some of the stuff I do all day.

    My compliments on a good QOTD.

  • Nice interesting question.   I managed to get it wrong by selecting 10 options instead of 9 - as well as the 9 correct ones I hadnumber 15 as the tenth.  I really must learn to count up to 9  😉.

    But number 15 is interesting on its own anyway.  It appears to demonstrate that the rules for implicit conversion for comparison in a where clause are sometimes different from those for comparisons in "if" statements and "searched case" expresions and also from those for matching in "simple case" statements (which are the same as for comparisons in search cases and if statements).  Maybe sql_variant values are special in that if they are explicitly converted neither the resulting value nor the thing they are being compared with can be implicitly converted in a comparison; but if so it's easily demonstrated that these special rules don''t apply in if statements and case expressions, so it still implies a special rule for comparisons in where clauses.

    The explanation provided for number 15 is either completely wrong (claiming something about comparison in general for an explicitly converted sql_variant) or it is carelessly making a statement about something that happens only in a "where" clause without explicitly stating that limitation of scope.  Or perhaps irrelevant because it's describing some other case (it refers to something  explicitly converted to varchar being compared with an sql variant containing an integer, but the only explicit conversion is of that sql_variant containing the integer and there's no way it's being compared with another sql_variant or the preconversion version of itself, it's being compared with a literal integer (and the comparison would deliver 'true' in anything but a where-clause context., as demonstrated by the code below).

    The code that demoonstrates that comparison and matching outside or where clauses doesn't word as in described in the explanation for 15 is very simple:-
    declare @S sql_variant = 123 ;
    if cast (@s as varchar(3)) = 123 select 'true' else select 'false' ;
    if 123 = cast (@s as varchar(3)) select 'true' else select 'false' ;
    select case cast(@s as varchar(3)) when 123 then 'true' else 'false' end;
    select case 123 when cast(@s as varchar(3)) then 'true' else 'false' end;

    All four lines  return "true",  demonstrating that either the result of explicitly converting the sql variant is immediately implicitly converted as required by the match or comparison, or the value wit which it is to be compared is automatically converted to the required value.

    Tom

  • Sean Lange - Friday, July 14, 2017 2:18 PM

    Jeff Moden - Friday, July 14, 2017 2:01 PM

    Stewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AM

    Sean Lange - Friday, July 14, 2017 7:36 AM

    This question was interesting but I found the scope way too broad for a single question. It would have been better to isolate a few examples instead of 15 queries to parse. And then maybe break this into a series of 3 or 4 questions regarding sql_variant and conversions.

    Agreed wholeheartedly.
    it is a good concept for a question,  though, enjoyed working it out..

    If you look at it as a training exercise (part of the mission of QOD) where you actually go look up things (like what an SQLVARIANT is) rather than as a question, I think it's rather good.

    From the learning perspective it is excellent. Just so many lines to parse mentally it makes it super tempting to just throw up your hands after the first 4 or 5.

    I tend to agree. The parsing of this question was a bit too much for a QOTD.

    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

Viewing 10 posts - 1 through 9 (of 9 total)

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