InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 00:47:32 GMT20http://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Brigadur (4/25/2011)[/b][hr]I answered "None of the above". After getting the explanation I still claim I answered right. The question was "... how many rows are gauranteed ...", so answer "Any of the above", well, questionable.Interesting question though[/quote]Not trying to beat the dead horse on this one but I have to agree. How can you say that a value is "Gauranteed" if another value of more or less (mostly the less is what bugs me) could also be true. Say you picked 9 (as in 9 rows are gauranteed) but the next exec you get 7, well then you could not have "Gauranteed" 9 since you didnt get 9 and therefore failed to meet your Gauranteed Value...I think thye should fix it to give credit for NONE as well.Wed, 29 Feb 2012 19:58:48 GMTbadatthishttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxIn SQL Server 2005, it won't give the answer other the syntax error since we have to use 'insert into table ' with 'union all' statement for the insertion to be done.Wed, 20 Jul 2011 02:41:28 GMTsumadevuhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]mister.magoo (6/27/2011)[/b][hr]Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.[/quote]Interestingly, the reason I chose to use the syntax I did is that most people assume that this type of query _has_ to be written using a CTE. There are two schools of thought on CTEs versus nested subqueries. I used to always use CTEs, but when there are several nested CTEs it can be be more convenient to debug written as nested subqueries - all you have to do is highlight a section of the code and run it; with nested CTEs you usually have to edit the query to select directly from whichever intermediate CTE you are interested in. Overall, though, it's purely a question of style; both are correct.Mon, 27 Jun 2011 19:32:04 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Ninja's_RGR'us (6/27/2011)[/b][hr][quote][b]mister.magoo (6/27/2011)[/b][hr][quote][b]Ninja's_RGR'us (6/27/2011)[/b][hr][quote][b]mister.magoo (6/27/2011)[/b]And the same thing re-written using a CTE - just to show an alternate way to format the code:[code="sql"];WITH ToDelete AS( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) DELETE ToDeleteOUTPUT deleted.*[/code]Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.[/quote]I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart![/quote]:-D I always enjoy words like that - hope they still make me chuckle when I am 90!I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).[/quote]I personally have set implicit_transactions ON by default in ssms.The first work I type IS ROLLBACK and then I push it down at the end of the script. That way I have to comment out rollback and then type commit to push the changes into the db. Saved my but once or twice so far.[/quote]Yeah - SSMS Tools does a similar job, when you open a new query, it stuffs BEGIN TRAN....ROLLBACK TRAN in there for you...Mon, 27 Jun 2011 08:01:45 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]mister.magoo (6/27/2011)[/b][hr][quote][b]Ninja's_RGR'us (6/27/2011)[/b][hr][quote][b]mister.magoo (6/27/2011)[/b]And the same thing re-written using a CTE - just to show an alternate way to format the code:[code="sql"];WITH ToDelete AS( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) DELETE ToDeleteOUTPUT deleted.*[/code]Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.[/quote]I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart![/quote]:-D I always enjoy words like that - hope they still make me chuckle when I am 90!I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).[/quote]I personally have set implicit_transactions ON by default in ssms.The first work I type IS ROLLBACK and then I push it down at the end of the script. That way I have to comment out rollback and then type commit to push the changes into the db. Saved my but once or twice so far.Mon, 27 Jun 2011 07:59:57 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Ninja's_RGR'us (6/27/2011)[/b][hr][quote][b]mister.magoo (6/27/2011)[/b]And the same thing re-written using a CTE - just to show an alternate way to format the code:[code="sql"];WITH ToDelete AS( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) DELETE ToDeleteOUTPUT deleted.*[/code]Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.[/quote]I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart![/quote]:-D I always enjoy words like that - hope they still make me chuckle when I am 90!I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).Mon, 27 Jun 2011 07:55:31 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]mister.magoo (6/27/2011)[/b]And the same thing re-written using a CTE - just to show an alternate way to format the code:[code="sql"];WITH ToDelete AS( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) DELETE ToDeleteOUTPUT deleted.*[/code]Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.[/quote]I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart!Mon, 27 Jun 2011 07:48:01 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]SQLkiwi (6/26/2011)[/b][hr]One can delete 9 rows [i]non-deterministically[/i] with this code:[code="sql"]DELETE TOP (9) @t[/code]Though in practice you're likely to see the same 9 rows deleted, it's just not guaranteed. To go further and delete 9 rows at random reliably:[code="sql"]DELETE ToDelete FROM( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) AS ToDelete;[/code]Now I know that wasn't really the point of the question, but I wanted to get it out of the way.The broader question is whether the optimizer should be free to reorder expressions and, in particular, to evaluate those expressions more than once, even when that expression is non-deterministic. On balance, I think I prefer better query plans for a broad class of queries over the alternative.[/quote]And the same thing re-written using a CTE - just to show an alternate way to format the code:[code="sql"];WITH ToDelete AS( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) DELETE ToDeleteOUTPUT deleted.*[/code]Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.Mon, 27 Jun 2011 07:44:05 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxOne can delete 9 rows [i]non-deterministically[/i] with this code:[code="sql"]DELETE TOP (9) @t[/code]Though in practice you're likely to see the same 9 rows deleted, it's just not guaranteed. To go further and delete 9 rows at random reliably:[code="sql"]DELETE ToDelete FROM( SELECT TOP (9) * FROM @t AS t ORDER BY NEWID()) AS ToDelete;[/code]Now I know that wasn't really the point of the question, but I wanted to get it out of the way.The broader question is whether the optimizer should be free to reorder expressions and, in particular, to evaluate those expressions more than once, even when that expression is non-deterministic. On balance, I think I prefer better query plans for a broad class of queries over the alternative.Sun, 26 Jun 2011 04:18:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxSurprised...Thu, 23 Jun 2011 20:12:34 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxJason,Great question with the exposure of NEWID(). I got it wrong but i learnt something today. I am not worried about the points we get here but am more interested towards learning every day from SQL Experts around the globe.Thanks again and look forward more questions. Thanks,AbiFri, 29 Apr 2011 12:34:24 GMTAbi Chapagaihttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxGood Question... Thank you...Thu, 28 Apr 2011 06:13:53 GMTJagadish Kumar Punnapuhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxI 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.Wed, 27 Apr 2011 02:39:26 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxWhat 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.Tue, 26 Apr 2011 07:23:10 GMTMr. Phantomblothttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]bitbucket-25253 (4/25/2011)[/b][hr]One of the main objectives of the QOD is to [b]teach/educate[/b] and that your QOD has certainly done.[/quote]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.Tue, 26 Apr 2011 07:18:39 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxi 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!!!!!!!!!!!!!!!!!!!! :-)Tue, 26 Apr 2011 05:38:07 GMTanu1krishnahttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Toreador (4/26/2011)[/b][hr]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".[/quote]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...Tue, 26 Apr 2011 03:34:56 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxIt 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".Tue, 26 Apr 2011 03:26:14 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxInteresting question. Thanks.Mon, 25 Apr 2011 23:30:09 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxJason, 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![code="sql"]DECLARE @t TABLE (nDex INT IDENTITY(1,1) not null primary key ,valu VARCHAR(9) ,keey UNIQUEIDENTIFIER)[/code]Mon, 25 Apr 2011 19:11:40 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Jason Selburg (4/25/2011)[/b][hr]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."[/quote]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 [b]teach/educate[/b] and that your QOD has certainly done.Mon, 25 Apr 2011 16:12:05 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxI'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."Mon, 25 Apr 2011 15:41:51 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxInteresting 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.Mon, 25 Apr 2011 13:49:35 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxVery 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?Mon, 25 Apr 2011 13:21:19 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Jason Selburg (4/25/2011)[/b][hr]I've asked Mr. Jones to mark both [b]Any [/b]and [b]All [/b]as valid answers. Unfortunately [u]the QOTD engine does not allow multiple "possible" answers.[/u][/quote]Well, these were not the choices. You probably meant to say "both [b]Any[/b] and [b]None[/b] as valid answers". This could be a great question if the correct answer was not based on semantics. True that [b]any[/b] of the above may return but [b]none[/b] is actually guaranteed, so it is difficult to figure out why [b]None of the above[/b] choice is not correct. If the question stated "[b]how many rows may be returned by the OUTPUT statement below?[/b]" 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 g[b]au[/b]ranteed which was probably meant to be g[b]ua[/b]ranteed instead.OlegMon, 25 Apr 2011 12:45:06 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote]In SQL Server 2008 how many rows are gauranteed to be returned by the OUTPUT statement below?[/quote]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.Mon, 25 Apr 2011 12:01:14 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Jason Selburg (4/25/2011)[/b][hr]One more time. :-)Can you agree that both of these code snippets SHOULD do the exact same thing! (Delete a random set of 9 rows)but they do NOT. That is the point here. [/quote]I get your point. I just don't agree with it. Even if I did agree with your point, the answer should be "none of the above" since 1 or 6 are a possible number of rows returned by your code.As for your last code example, I see why they do not do the exact same thing. Similar code does not work reliably on other SQL platforms either.It has been my understanding that a derived table statement is executed in line, not seperately. This may not be the perfect way to explain it, but it works for me. I look at the code that "does not work" and see it is trying to update a derived table with new random values output from a system function and and then order the derived table on those values in the same statement that it is deleting values from the source table. Since this is executed in line with the delete statement, you get some very random unrepeatble output. NEWID() can not be executed against every row of the derived table unless it is executed in a seperate SQL statement.Mon, 25 Apr 2011 11:50:36 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Jason Selburg (4/25/2011)[/b][hr]It is a question of semantics. You are guaranteed to have ANY of those values and NONE of them at the same time. There is no guarantee, and yet there is one.**YES THIS IS A TRICK QUESTION**It's intent was to get you thinking, and spark conversation on the validity of Microsoft's statement that this is by design and not a bug.I've asked Mr. Jones to mark both [b]Any [/b]and [b]All [/b]as valid answers. Unfortunately [u]the QOTD engine does not allow multiple "possible" answers.[/u][/quote]Let's take a poll...88% of us think you're wrong :w00t:.Thanks for the question... let's stop kicking that dead horse. We all got our points and let's move on :hehe:.Mon, 25 Apr 2011 11:37:42 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxI would say this is an interesting question.Mon, 25 Apr 2011 11:02:59 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx*******I think the wording could be fixed. Since the question asks what is the quarenteed results it should be none of the above. A Quarentee is a certainty not a random set of rows. any of the above suggests it is limited to the three options. I think a lot of people got it wrong becuase logically none of the answers seem to fit if you are asking what the quarenteed outcome would be.******Never mind I see that this has been discuss widly here already. I should have read the other posts first.I guess I can see both sides but I hate seeing the incorrect on my screen. :crying:I will pout in my corner now :-PMon, 25 Apr 2011 11:01:06 GMTDan.Humphrieshttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxI am definitely in the "none of the above" camp. There is a syntactical ambiguity in the question posed. Should the question be interpreted as "Exactly how many rows will be returned by the OUTPUT statement?" or should it be interpreted as "What is the minimum number of rows which will be returned by the OUTPUT statement?"? (2, 9, or 11) is not a valid answer to the latter interpretation, and IN (2, 9, 11) is not a complete answer to the former interpretation. The given answer "any of the above" is inconsistent with the explanation, which gives the answer as "IN (0,1,...11)"My natural language interpretation of the OUTPUT statement would be "For each row in t, randomly select nine rows of the table t. If the row is included in this selection, delete it. Output the rows deleted." In this light, "IN (0,1,...,11)" seems like the expected range of row counts. As such, this example does not really seem to demonstrate the reported bug.Tim BaileyMon, 25 Apr 2011 10:43:44 GMTtbailey 19088http://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxInteresting question. Although I didn't get it right, it did make me do more research on the topic.Mon, 25 Apr 2011 10:02:30 GMTc00578http://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxSince you don't know what the number of items will be returned you can't guarantee 2, 9 or 11. So it's not "any of the above" and the correct answer must be "none of the above". :-)Mon, 25 Apr 2011 09:34:27 GMTJon McCallhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxI get the intent of this question, but it boils down to semantics and how the reader subjectively interprets the question. If most people mark the wrong answer, there is something fundamentally wrong with the question being posed.Mon, 25 Apr 2011 09:33:03 GMTsixthzenzhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxOne more time. :-)Can you agree that both of these code snippets SHOULD do the exact same thing! (Delete a random set of 9 rows)[code]CREATE TABLE #t (nDex INT IDENTITY(1,1) ,valu VARCHAR(9) ,keey UNIQUEIDENTIFIER)INSERT #tVALUES ('a',NEWID()) , ('b',NEWID()) ,('c',NEWID()) , ('d',NEWID()) ,('e',NEWID()) , ('f',NEWID()) ,('g',NEWID()) , ('h',NEWID()) ,('i',NEWID()) , ('j',NEWID()) ,('k',NEWID()) DELETE tOUTPUT DELETED.*FROM #t AS t INNER JOIN (SELECT TOP 9 nDex FROM #t ORDER BY NEWID()) AS b ON b.ndex = t.nDexDROP TABLE #tGO[/code].. and ..[code]DECLARE @t TABLE (nDex INT IDENTITY(1,1) ,valu VARCHAR(9) ,keey UNIQUEIDENTIFIER)INSERT @tVALUES ('a',NEWID()) , ('b',NEWID()) ,('c',NEWID()) , ('d',NEWID()) ,('e',NEWID()) , ('f',NEWID()) ,('g',NEWID()) , ('h',NEWID()) ,('i',NEWID()) , ('j',NEWID()) ,('k',NEWID()) DELETE tOUTPUT DELETED.*FROM @t AS t INNER JOIN (SELECT TOP 9 nDex FROM @t ORDER BY NEWID()) AS b ON b.ndex = t.nDexGO[/code]but they do NOT. That is the point here. Not that the code can be written "better" or any other way. The point is, that ordering by NEWID in an inline query that uses a table variable is not consistent with what happens when you use a temp table.** side note, the [u]keey [/u]column is there for fluff and has no purpose.Mon, 25 Apr 2011 08:27:58 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]SanDroid (4/25/2011)[/b][hr]Seems this only happens If you order the output of a radom set by a row of newid values being created during the same transaction to delete values from the set. I would call the script buggy and write it in a way it worked long before I engaged microsoft to resolve something that IMHO: is working as expected.[/quote]It's actually a rather simple change, in this case, to get a consistent count.[code="sql"]DECLARE @t TABLE (nDex INT IDENTITY(1,1) ,valu VARCHAR(9) ,keey UNIQUEIDENTIFIER)INSERT @tVALUES ('a',NEWID()) , ('b',NEWID()) ,('c',NEWID()) , ('d',NEWID()) ,('e',NEWID()) , ('f',NEWID()) ,('g',NEWID()) , ('h',NEWID()) ,('i',NEWID()) , ('j',NEWID()) ,('k',NEWID()) DELETE tOUTPUT DELETED.*FROM @t AS t INNER JOIN (SELECT TOP 9 nDex FROM @t ORDER BY keey) AS b ON b.ndex = t.nDexGO[/code]Since it's a NEWID already just sort by keey. If there isn't a NEWID column already one could be added and then used, asssuming it doesn't have the same bug.Mon, 25 Apr 2011 08:19:22 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Jason Selburg (4/25/2011)[/b][hr]The behavior displayed here does not occur with temp tables, only table variables and NEWID(). That is what is being shown here. The "logic" of the code makes one expect that nine rows are to be deleted at random and they are not. THIS IS MY POINT :-D[/quote]The "logic" of the code would not pass Code analysis here becuase it is deleting from and joining to the same table while sorting it with a newly generated cloumn of values in the same transaction. However a join to a temp table, or using the existing NewID() column values works and seems very random.Does this code not give the same result as ordering by a set of newly generated values using a derived table statement?There are many differant code paterns that code analysis and good testing can be used to make sure code works as expected. I see this as another one one of them. I see this code executing as expected. I guess that is why when I got 8 rows in my result set "None of the above" was the right answer. Obviously someting is not working as expected. :cool:Mon, 25 Apr 2011 08:16:47 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]Jason Selburg (4/25/2011)[/b][hr]**YES THIS IS A TRICK QUESTION**It's intent was to get you thinking, and spark conversation on the validity of Microsoft's statement that this is by design and not a bug.[/quote]Seems this only happens If you order the output of a radom set by a row of newid values being created during the same transaction to delete values from the set. I would call the script buggy and write it in a way it worked long before I engaged microsoft to resolve something that IMHO: is working as expected.Unless I was trying to write a trick question that would get more than 90% wrong answers. Those seem to be real popular these days.Mon, 25 Apr 2011 07:58:39 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspxI don't claim to be a SQL guru by any means, but I disagree with the answer to this question. Sure it could be any of the values presented, but you can't guarantee that it will be one of those three values as it very well could be 6, 8, or 10, for example. I think the problem most of us had with this question was the word choice: 'guarantee' implies that it would have to be one of the supplied answers and no others. Aside from that, good question.Mon, 25 Apr 2011 07:57:50 GMTSSNewbiehttp://www.sqlservercentral.com/Forums/Topic1097891-304-1.aspx[quote][b]SanDroid (4/25/2011)[/b][hr][quote][b]bitbucket-25253 (4/24/2011)[/b][hr]Oh well, another question that does not rely on the workings of the database engine, but rather english comprehension.[/quote]I would say you comprehended just fine. I would also say I do not see a concept or point being displayed by the question or it's explination. After furthur review..............................Seems that if you ORDER BY the name or alias of the column holding the NEWID() values and join to a #temp table of the records instead of the table you are deleting from (as you delete) the code always returns as I would expect it too.The other code would seem to never produce the same amount of joinable values to return... So why is "none of the above" the wrong answer?[/quote]The behavior displayed here does not occur with temp tables, only table variables and NEWID(). That is what is being shown here. The "logic" of the code makes one expect that nine rows are to be deleted at random and they are not. THIS IS MY POINT :-DMon, 25 Apr 2011 07:50:52 GMTJason Selburg