November 3, 2014 at 7:55 am
Yes, excellent question, but the answers should've been consolidated into 3-4 possibilities to make the correct answer less obvious
November 3, 2014 at 7:59 am
Aleksl-294755 (11/3/2014)
Good question. Never thought about using the second option. I learned something. Thanks!
Same here. Thanks.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 3, 2014 at 8:03 am
trevor.ball (11/3/2014)
...to make the correct answer less obvious
Which correct answer? There are three of them:
a) Query 1 will run successfully [true]
b) Query 2 will run successfully [also true]
c) Both will give same result [can only be true if a and b are also true, which is the only reason I didn't pick this obviously correct answer]
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
November 3, 2014 at 10:08 am
I learned something new today. However, I don't think I will ever be using the second option because I don't think most people would understand that it works exactly the same.
November 3, 2014 at 10:37 am
I have used in before and find it useful. Good question.
M.
Not all gray hairs are Dinosaurs!
November 3, 2014 at 12:14 pm
ronmoses (11/3/2014)
Yes, I agree this seemed fairly obvious, but the fact that there are three correct answers (the first three options are all true) and I'm only permitted to choose one of them led me to conclude it must be none of those, since it could not be all of those. Not a well-designed question at all.
Technically, the first three options would only be valid correct answers if a multi-select had been employed. But since a single select is employed, then options 1 and 2 are wrong because they are not entirely correct. That means only 1 correct answer is presented and the design of the question is just fine.
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
November 4, 2014 at 1:10 am
Great question and nice alternative way to write an OR.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 4, 2014 at 4:21 am
Interesting. will 0 in ('foo','bar') use indexes?
November 4, 2014 at 6:27 am
I'm also in the camp of never thinking of writing it the second way. I guess it's a lesson in not being locked into one way of doing things.
November 4, 2014 at 1:26 pm
SQLRNNR (11/3/2014)
ronmoses (11/3/2014)
Yes, I agree this seemed fairly obvious, but the fact that there are three correct answers (the first three options are all true) and I'm only permitted to choose one of them led me to conclude it must be none of those, since it could not be all of those. Not a well-designed question at all.Technically, the first three options would only be valid correct answers if a multi-select had been employed. But since a single select is employed, then options 1 and 2 are wrong because they are not entirely correct. That means only 1 correct answer is presented and the design of the question is just fine.
1 & 2 are also impossible because of 4 & 5. If option 1 is the correct answer, it implies that query 2 must fail, so option 5 must also be correct. Therefore, option 1 cannot be true. Similarly, for Option 2 to be the correct answer, it implies that query 1 must fail, so option 4 must also be correct. Therfore, option 2 cannot be true. Options 4 and 5 are excluded under the same logic. This leaves just option 3 being correct, no matter what the question was. An exercise in logic, not in SQL knowledge.
But I did learn a new way of using the IN clause. It never occurred to me to use it in this way.
November 11, 2014 at 5:04 pm
jdauphine (11/4/2014)
Interesting. will 0 in ('foo','bar') use indexes?
Since 0, 'foo' and 'bar' are all constant values, it will not. I didn't run it, but I expect an error because of a failed conversion attempt (SQL Server will try to convert the strings to integers for the comparison). I also expect that this error is raised at compile time, when the optimmizer tries to remove fallacies.
If you meant to ask about "0 IN (Column1, Column2)", then the answer is the same as for "0 = Column1 OR 0 = Column2", because IN is defined as a shorthand for a series of OR expressions. Which means that on a very large table and with very selective indexes on each of the columns, you *might* get a plan that uses two index seeks and a union.
January 16, 2015 at 6:03 am
nice one, but unnecessary exercise as both will have same plans.
January 21, 2015 at 10:44 am
Thanks - is this true all the way back to 2005?
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply