April 25, 2011 at 12:01 pm
In SQL Server 2008 how many rows are gauranteed to be returned by the OUTPUT statement below?
With the awesomeness or NEWID I figured it could be any number of possible values, since that means that the answers other than none above were not guaranteed,
So I ran it a few times... I got 7 rows on the first run. Which confirmed my answer that None of them could be guaranteed.
Alas... I failed at reading the author's mind.
April 25, 2011 at 12:45 pm
Jason Selburg (4/25/2011)
I've asked Mr. Jones to mark both Any and All as valid answers. Unfortunately the QOTD engine does not allow multiple "possible" answers.
Well, these were not the choices. You probably meant to say "both Any and None as valid answers". This could be a great question if the correct answer was not based on semantics. True that any of the above may return but none is actually guaranteed, so it is difficult to figure out why None of the above choice is not correct. If the question stated "how many rows may be returned by the OUTPUT statement below?" then I would really like it (you can use the checkboxes rather than radio buttons to allow multiple correct choices).
By the way, I submitted the QotD in the past and understand that it takes some time for the question to be published. This means that the author has a plenty of time to edit the wording and fix the spelling errors such as in the word gauranteed which was probably meant to be guaranteed instead.
Oleg
April 25, 2011 at 1:21 pm
Very interesting question. Completely wrong answer ("none of the above" is the only correct answer, assuming that the question is actually in English and no some strange language that looks like English but has radically different semantics). Good explanation and extremely intersting reference. Overall a good QoTD despite the wrong answer. (My answer was neither the right one nor the one stated to be correct anyway; I didn't know what would happen so went for the obvious option.)
On the underlying issue being got at, I find the MS approach completely unacceptable. The optimiser should never change the semantics - but there's nothing wrong with defining the semantics so that the optimiser doesn't have to; in this case MS could change the language definition so that the semantics of this sort of select (it's the select clause side of the join that's the problem) states that there is an indeterminate result. Then there wouldn't be a problem. But it looks as if MS's SQL team are too lazy to update the T-SQL spec and document the semantics that the system actually delivers, and their response even appears to say that they think it would be ok to have the optimiser modify the semantics even if the semantics were clearly documented. I've noticed this somewhat cavalier attitude to the language before - and this indicates that it's not improving. How soon before this "the optimiser is holy and overrides everything else and can do no wrong" is modified by a commitment to document what the semantics of the language actually is and give notice when they intend to change the semantics?
Tom
April 25, 2011 at 1:49 pm
Interesting question. I opted to try this query out a few times before answering because I hadn't really played with the newid() function yet. Too bad the correct answer was not the one listed as correct....
Thanks for submitting a QOTD anyway! I learned something regardless.
April 25, 2011 at 3:41 pm
I'll concede that the use of the word Guarantee was not the correct word.
But I will again state, that the purpose was to spark conversation.
My appologies to everyone who has been harmed by this. :hehe:
And I will also concede that the only real answer is "the result is unpredictable."
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 25, 2011 at 4:12 pm
Jason Selburg (4/25/2011)
I'll concede that the use of the word Guarantee was not the correct word.But I will again state, that the purpose was to spark conversation.
My appologies to everyone who has been harmed by this. :hehe:
And I will also concede that the only real answer is "the result is unpredictable."
Jason,
Do not feel bad, as one who has been beaten from pillar to post on the wording of my submitted QODs, look at it on the bright side. One of the main objectives of the QOD is to teach/educate and that your QOD has certainly done.
April 25, 2011 at 7:11 pm
Jason, good question - not going to bother commenting on the answer as the question is the important bit.
For the record, (as part of the general discussion of the issue raised here) there is a simple fix in this case, which is to add an OPTION(HASH JOIN) to the query, just in case anyone ever needs a quick and dirty workaround...
Thanks for enlightening me!
(Thankfully it does make sense to me that it works this way, although I cannot fathom why the same is not true for temp tables and real tables!)
EDIT: Just noticed that making nDex the PRIMARY KEY ALSO fixes it....or seems to at least - there are no guarantees here!
DECLARE @t TABLE
(nDex INT IDENTITY(1,1) not null primary key
,valu VARCHAR(9)
,keey UNIQUEIDENTIFIER)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 25, 2011 at 11:30 pm
Interesting question. Thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 26, 2011 at 3:26 am
It can be really annoying when someone posts an interesting question, but the motivation behind it gets virtually ignored as people moan on with repeated posts complaining about the wording.
Who cares whether it's 'any' or 'none'? The point is surely that the result is not what would be logically expected, and not what you'd get if using anything other than table variables. And that MS are claiming that this is acceptable behaviour. Even after re-reading their justification I don't understand what they mean - I suspect it's something aloing the lines of "it's too much work for us to fix it".
April 26, 2011 at 3:34 am
Toreador (4/26/2011)
It can be really annoying when someone posts an interesting question, but the motivation behind it gets virtually ignored as people moan on with repeated posts complaining about the wording.Who cares whether it's 'any' or 'none'? The point is surely that the result is not what would be logically expected, and not what you'd get if using anything other than table variables. And that MS are claiming that this is acceptable behaviour. Even after re-reading their justification I don't understand what they mean - I suspect it's something aloing the lines of "it's too much work for us to fix it".
I read their explanation as "How could we possibly know that you want to execute the subquery once" - which to my mind is answered by the words "INNER JOIN" rather than "CROSS APPLY" - but there you go.
Seeing as there are easy methods to avoid the problem, I am not personally that bothered by their response - I would much rather they spent their time on more interesting/useful things...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 26, 2011 at 5:38 am
i also chosen none of the above which actully should be the answer 🙂 also i am not at all satisfied with the explanation saying that it is a bug and can't fixed 🙂
what is the use of rasing such questions which itself a question?
and prompting others that u are wrong!!!!!!!!!!!!!!!!!!!! 🙂
April 26, 2011 at 7:18 am
bitbucket-25253 (4/25/2011)
One of the main objectives of the QOD is to teach/educate and that your QOD has certainly done.
I agree with this. My comment about how the query could be modified so it'll always remove 9 rows was more from the attitude of, "If someone is dealing with this issue here's one way to handle it," and less from, "the question was poor." I certainly didn't know about this behavior before and it's good to know it's out there.
April 26, 2011 at 7:23 am
What about the interesting issues raised by the question?
To me, it is strange why the sqlserver team is avoiding adding an option to materialize intermediate tables from CTE's. Oracle has this with the /*+ materialize */ hint.
If we could have the option to get rid of the automatic expansion of inline views/CTEs/TVF, we could control the query execution by query semantics instead of fighting stale statistics with query hints.
A person writing query, will often know instinctively the best sequence of steps to execute a query would be.
"I'll get my few candidates from this CTE1, and then get some more data with CTE2 that consumes CTE1 and then I'll join a little more. "
To get this to perform consistently, you'll often see developers make up all kinds of #temp tables etc, because the got burned by the optimizer one time to many.
Its no secret that the optimizer will generate strange plans when there are a lot of joins and the statistics a not as fresh as they could be.
On Oracle, /*+ materialize */ did the trick. On sqlserver you'll have to do the materializing yourself, making the code less readable and maintainable.
April 27, 2011 at 2:39 am
I think its a a nice question, but wrong answer and explanation. I would say that NONE of the above should be the correct answer, because none are guaranteed.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
April 28, 2011 at 6:13 am
Good Question...
Thank you...
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply