Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

OUTPUT & NEWID() Expand / Collapse
Author
Message
Posted Monday, April 25, 2011 7:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
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
Post #1098090
Posted Monday, April 25, 2011 7:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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.
Post #1098092
Posted Monday, April 25, 2011 7:29 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:15 AM
Points: 1,447, Visits: 1,059
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.
Post #1098094
Posted Monday, April 25, 2011 7:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
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
Post #1098095
Posted Monday, April 25, 2011 7:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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
Post #1098097
Posted Monday, April 25, 2011 7:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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?
Post #1098101
Posted Monday, April 25, 2011 7:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
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


______________________________________________________________________

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
Post #1098109
Posted Monday, April 25, 2011 7:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 2, 2013 5:45 PM
Points: 60, 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.
Post #1098114
Posted Monday, April 25, 2011 7:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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.
Post #1098115
Posted Monday, April 25, 2011 8:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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


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.
Post #1098129
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse