July 14, 2017 at 12:09 am
Comments posted to this topic are about the item Comparing with SQL_VARIANT
God is real, unless declared integer.
July 14, 2017 at 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.
_______________________________________________________________
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/
July 14, 2017 at 11:12 am
Sean Lange - Friday, July 14, 2017 7:36 AMThis 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”
July 14, 2017 at 2:01 pm
Stewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AMSean Lange - Friday, July 14, 2017 7:36 AMThis 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
Change is inevitable... Change for the better is not.
July 14, 2017 at 2:18 pm
Jeff Moden - Friday, July 14, 2017 2:01 PMStewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AMSean Lange - Friday, July 14, 2017 7:36 AMThis 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/
July 14, 2017 at 2:28 pm
Sean Lange - Friday, July 14, 2017 2:18 PMJeff Moden - Friday, July 14, 2017 2:01 PMStewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AMSean Lange - Friday, July 14, 2017 7:36 AMThis 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
Change is inevitable... Change for the better is not.
July 14, 2017 at 3:18 pm
Jeff Moden - Friday, July 14, 2017 2:28 PMSean Lange - Friday, July 14, 2017 2:18 PMJeff Moden - Friday, July 14, 2017 2:01 PMStewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AMSean Lange - Friday, July 14, 2017 7:36 AMThis 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/
July 14, 2017 at 7:49 pm
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.
July 16, 2017 at 11:11 am
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-2 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
August 22, 2017 at 11:33 am
Sean Lange - Friday, July 14, 2017 2:18 PMJeff Moden - Friday, July 14, 2017 2:01 PMStewart "Arturius" Campbell - Friday, July 14, 2017 11:12 AMSean Lange - Friday, July 14, 2017 7:36 AMThis 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