July 25, 2011 at 10:14 am
Yeah, yeah, yeah. "It depends", "it depends", "it depends"...
Since that wasn't one of the choices given, we have to put aside our nit-pickers hats and take the question at face value. Even without the apparent typo of ID vs ModelID (and don't you think BitBucket wishes now that he'd not changed his copy/paste from BOL?), the current data obviously would affect a real-world answer. But we don't have the data and "it depends" ain't in the list of choices.
So, Get Over It. Did you recognize that parentheses would be needed to test models 20 and 21 together for being red? Or were you like me and thought "oh, crap. I always use parentheses so I don't have to think about precedence rules", then had to look them up to be sure of an answer?
This was a good one for me as it reinforced a basic that I keep letting slip from memory: arithmetic first, then NOT, then AND and finally OR. I always seem to misremember OR as before AND.
---- Edit: I wrote and posted before reading the excellent rejoinder to the complainers from Jimmo right above this one. Sorry I'd lost patience and didn't read all before posting.
Thank you, BitBucket.
July 25, 2011 at 10:38 am
sjimmo (7/25/2011)
In one run against a table with 700M rows, I receive 4513066 rows covering all dates where number = 1. In the other I receive only 13935 records for the specific date but both numbers. Do you need the data as well or can you figure out the answer?
If I asked you how many rows those queries would return BEFORE you ran them, would you be able to tell me without looking at the data?
Do you agree that if there were no rows in the table, both queries would return the same number of rows (0)?
Do you agree that the other examples cited in http://qa.sqlservercentral.com/Forums/FindPost1147503.aspx would also result in the queries returning the same number of rows?
In each of these instances, knowledge of the data is necessary to get to the correct answer.
July 25, 2011 at 10:57 am
Thanks for posting the question
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
July 25, 2011 at 11:46 am
I have corrected the typo and rewritten the question a bit. I've awarded back points as well
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 25, 2011 at 11:53 am
I have corrected the typo and rewritten the question a bit. I've awarded back points as well
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 25, 2011 at 11:58 am
Steve Jones - SSC Editor (7/25/2011)
I have corrected the typo and rewritten the question a bit. I've awarded back points as well
Is that some sort of special deal where we get doubles points back on a "bad" question ? :hehe:
July 25, 2011 at 12:32 pm
sknox
If I asked you how many rows those queries would return BEFORE you ran them, would you be able to tell me without looking at the data?
The answer is no, only because there is over 1/2 billion rows.
Do you agree that if there were no rows in the table, both queries would return the same number of rows (0)?
Do you agree that the other examples cited in http://qa.sqlservercentral.com/Forums/FindPost1147503.aspx would also result in the queries returning the same number of rows?
Agreed, but know you have chosen to remain childish and argumentative because you are not reading the intent of the question.
In each of these instances, knowledge of the data is necessary to get to the correct answer.
For the intent of the question again why do you need the data? The question really is about what happens when you have parentesis around the or clause and what happens when you do not?
The point is obviously very important to you, and you have more than made it up. It is too bad that you cannot see beyond that.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
July 25, 2011 at 1:20 pm
I should read more closely as I didn't see the error in the WHERE clause. I was looking at the parentheses and answered correctly. Thanks for the question.
http://brittcluff.blogspot.com/
July 25, 2011 at 2:06 pm
Got it right because I actually wrote out test scenarios on paper and proved that the order of operations could result in different answers depending on the data. I had my fingers crossed that my imaginary data looked like the author's imaginary data though! 🙂
July 25, 2011 at 2:19 pm
I totally blow this one, I thought he was referring to the Adventureworks DB that comes with all 3 SQL versions and some of the fields where not in the DB so I said yes. A real ID10T error.:-D
July 25, 2011 at 2:21 pm
Why bother ?
The two WHERE clauses are NOT equivalent. No need for test data. You do not code based on known test data.
The only valid answer is that the two SQL statements cannot be guaranteed to always yield the same results.
July 25, 2011 at 5:20 pm
When I came to this question the typo was corrected (neither where clausementioned ID), it was specified that there was data in the table, and that magic word "always" was there in a position that made it clear that the question was one abot a generalisation - were the two results the same regardless of what the data was (so it wasn't actually necessary to specify that there was some data, "always" fixed that issue - I guess Steve added the have data bit to defuse some of the more bizarre comments). That made it an unambiguous question, and since AND has higher (level 6 is higher than level 7 - incidentally, am I the only one to think MS has the level numbering upside down here?) the brackets in the second version do make a difference so the answer has to be NO.
But the comments made me go did in my email and find the original question (today - like most days - I used the weekday email only to indicate articles, and navigated to QoTD when I connected to the website rather than getting the question from the email; but the comments made me go back to the email). Before the changes, I think I would have thought that because the data wasn't specified "do they return the same number of rows" had to mean "do they return the same number of rows regardless of the data"; I don't see what else it could have meant. And then the answer would have been NO trivially, since one query depended on what values ID had in some rows while the other one didn't. At least I hope that's what I would have thought. So the corrections to the question made no difference to the answer - and I hope I would have been bright enough to reach the right conclusion.
Tom
July 25, 2011 at 9:29 pm
sknox (7/25/2011)
sjimmo (7/25/2011)
In one run against a table with 700M rows, I receive 4513066 rows covering all dates where number = 1. In the other I receive only 13935 records for the specific date but both numbers. Do you need the data as well or can you figure out the answer?If I asked you how many rows those queries would return BEFORE you ran them, would you be able to tell me without looking at the data?
Do you agree that if there were no rows in the table, both queries would return the same number of rows (0)?
Do you agree that the other examples cited in http://qa.sqlservercentral.com/Forums/FindPost1147503.aspx would also result in the queries returning the same number of rows?
In each of these instances, knowledge of the data is necessary to get to the correct answer.
The question asked do both of these SELECT statements always return the same number of rows?
The answer is no because with each run the data could change and result in different results. It does depend on the data at the time to queries are run, however, how often does data stay static in a production system?
July 26, 2011 at 1:33 am
The answer is no because with each run the data could change and result in different results. It does depend on the data at the time to queries are run, however, how often does data stay static in a production system?
Actually it depends upon the parentesis. The or clause when placed inside the parentesis will be performed first and then the and clause will be ferformed.
The examples are bad in the answer provided because regardless of version, the result set will be different for each select.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
July 26, 2011 at 2:18 am
Lynn Pettis (7/25/2011)
The question asked do both of these SELECT statements always return the same number of rows?
It does now, but didn't originally. The problem with making corrections to questions after they've been published is that people end up arguing based on different questions!
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply