<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Jason Selburg
»
OUTPUT & NEWID()
56 posts, Page 2 of 6
««
1
2
3
4
5
»
»»
OUTPUT & NEWID()
Rate Topic
Display Mode
Topic Options
Author
Message
Jason Selburg
Jason Selburg
Posted Monday, April 25, 2011 7:25 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582,
Visits: 3,552
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
SanDroid
SanDroid
Posted Monday, April 25, 2011 7:25 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232,
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
Rose Bud
Rose Bud
Posted Monday, April 25, 2011 7:29 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:42 AM
Points: 1,072,
Visits: 1,026
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
Jason Selburg
Jason Selburg
Posted Monday, April 25, 2011 7:30 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582,
Visits: 3,552
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
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, April 25, 2011 7:31 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 21,357,
Visits: 9,540
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
SanDroid
SanDroid
Posted Monday, April 25, 2011 7:40 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232,
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
Jason Selburg
Jason Selburg
Posted Monday, April 25, 2011 7:50 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582,
Visits: 3,552
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
SSNewbie
SSNewbie
Posted Monday, April 25, 2011 7:57 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 8:51 AM
Points: 60,
Visits: 110
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
SanDroid
SanDroid
Posted Monday, April 25, 2011 7:58 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232,
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
SanDroid
SanDroid
Posted Monday, April 25, 2011 8:16 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232,
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 »
56 posts, Page 2 of 6
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
