SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OUTPUT & NEWID()


OUTPUT & NEWID()

Author
Message
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7042 Visits: 4115
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 Any and All as valid answers. Unfortunately the QOTD engine does not allow multiple "possible" answers.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
SanDroid
SanDroid
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2456 Visits: 1046
QOTD reference says
there can be any number of rows from ZERO to the number of rows in the table being deleted from.


Which matches what I expected and what I saw when running the answer code.

Why is "none of the above" not the right answer.

I would like to see that explination.
Even a good understanding of english as a first language does not help figure out this.

Nice concept for a question, but seems the scoring in flawed.
Wow
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1062
Interesting question. I had no problem with the semantics of the question or answer.

Wondering if anyone can think of a real world example where one might want to use "ORDER BY NEWID()?" Just curious.
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7042 Visits: 4115
wware (4/25/2011)
Interesting question. I had no problem with the semantics of the question or answer.

Wondering if anyone can think of a real world example where one might want to use "ORDER BY NEWID()?" Just curious.


It's a nifty way to randomize the order of a result set.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69071 Visits: 9671
wware (4/25/2011)
Interesting question. I had no problem with the semantics of the question or answer.

Wondering if anyone can think of a real world example where one might want to use "ORDER BY NEWID()?" Just curious.


I did when doing reindexing during business hours. I wanted to make sure to not lock to same table over and over again so newid() on the final query made the cut... especially combined with waitfor delay
SanDroid
SanDroid
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2456 Visits: 1046
bitbucket-25253 (4/24/2011)
Oh well, another question that does not rely on the workings of the database engine, but rather english comprehension.


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

DECLARE @t TABLE
(nDex INT IDENTITY(1,1)
,valu VARCHAR(9)
,keey UNIQUEIDENTIFIER)




INSERT @t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())

SELECT *
INTO #gT from @t;

DELETE t
OUTPUT DELETED.*
FROM @t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM #gT
ORDER BY keey) AS b
ON b.ndex = t.nDex;

DROP TABLE #gT;
GO

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?
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7042 Visits: 4115
SanDroid (4/25/2011)
bitbucket-25253 (4/24/2011)
Oh well, another question that does not rely on the workings of the database engine, but rather english comprehension.


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?


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

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
SSNewbie
SSNewbie
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 121
I 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.
SanDroid
SanDroid
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2456 Visits: 1046
Jason Selburg (4/25/2011)
**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.

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.
SanDroid
SanDroid
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2456 Visits: 1046
Jason Selburg (4/25/2011)

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


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search