OUTPUT & NEWID()

Question

Post reply

OUTPUT & NEWID()

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 1

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 2

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

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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

Ken SSCoach Points: 17191 More actions · Answer 4

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.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

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. Selburg

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

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);

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 8

Interesting question. Thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11375 More actions · Answer 9

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".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

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);

anu1krishna Mr or Mrs. 500 Points: 584 More actions · Answer 11

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!!!!!!!!!!!!!!!!!!!!

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 12

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.

Mr. Phantomblot SSC Veteran Points: 205 More actions · Answer 13

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.

SQLWinther SSCertifiable Points: 6294 More actions · Answer 14

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

Jagadish Kumar Punnapu SSChasing Mays Points: 645 More actions · Answer 15

Jagadish Kumar Punnapu

SSChasing Mays

Points: 645

April 28, 2011 at 6:13 am

#1317190

Good Question...

Thank you...

OUTPUT & NEWID()

Cookies on SQLServerCentral