SELECT TOP

Question

SELECT TOP

peter.skoglund

SSC Enthusiast

Points: 113
More actions
December 20, 2010 at 9:04 pm

#244500

Comments posted to this topic are about the item SELECT TOP
December 20, 2010 at 11:47 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Nice question.

MSDN link for the same: http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.105).aspx

Thanks

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

I'm confused: the correct answer is the row that is returned "most of the time"?

If yes, then it isn't a good question, as other answers might be possible. If you can receive other rows with TOP 1, then you should have added an 'It depends' answer.

For the rest: nice question 🙂 (also good layout and structure of code)

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

Carlo Romagnano SSC-Insane Points: 22663 More actions · Answer 4

da-zero (12/21/2010)
I'm confused: the correct answer is the row that is returned "most of the time"?
If yes, then it isn't a good question, as other answers might be possible. If you can receive other rows with TOP 1, then you should have added an 'It depends' answer.
For the rest: nice question 🙂 (also good layout and structure of code)

I agree with you: no "order by", no default sequence.

But what does this mean?

Always use the top with an ORDER BY clause!

Syntax should be appropriate to the hints.

paul s-306273 SSChampion Points: 10730 More actions · Answer 5

Nice question and layout, but surely the answer should be 'cannot be determined' without the 'order by' clause.

cengland0 SSCertifiable Points: 6102 More actions · Answer 6

I didn't like this question. I did get it right because I figured it would return 'Ann' most of the time but as the author said, it cannot be guaranteed without an order by clause. There should have been an option that said "Unknown" or "Cannot be 100% certain".

paul.knibbs SSCoach Points: 15320 More actions · Answer 7

I was expecting the answer to be something along the lines of things are always returned in the clustered index order (which would obviously raise questions in any case), but then it's talking about it being indeterminate--if that's the case, why isn't there an "it depends" answer? Question implies one thing, answer says another!

jts2013 Hall of Fame Points: 3226 More actions · Answer 8

Definitely should be "it depends"... I think the question is trying to show what happens when you add a clustered index to a heap and how the data is likely to get re-organised as the index is built.

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 9

For educational purposes, I would genuinely appreciate it if one of the folks who take issue with the "most of the time" factor could illustrate a scenario in which that script returns different results.

Thanks!

Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

sjimmo SSChampion Points: 11139 More actions · Answer 10

Good question - but, I think the answers and explanation are lacking.

The order of the clustered index is based upon the columns which make up the clustered index. (Change the field on the index to see this work.) The order is also based upon the collation of the database as well (simple enough to test, but add more data for a real test). Thus the TOP record returned is determined by these factors.

Thus, without any other definition of the collation, I would have to agree with those calling for an It depends answer.

Some good information can bee seen at http://msdn.microsoft.com/en-us/library/cc917672.aspx

Additional reading: http://msdn.microsoft.com/en-us/library/ms177443.aspx

or http://searchsqlserver.techtarget.com/tip/Clustered-and-non-clustered-indexes-in-SQL-Server

There are many other references out there as well.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Rich Mechaber SSChampion Points: 11130 More actions · Answer 11

I also got it right but don't like the available answers.

If MS and T-SQL experts emphasize that the only reliable way to return a result set in a specified order is for the last (i.e., outer) SELECT to include an ORDER BY clause, why would we want to have a question like this?

I like a straightforward QoD that teaches one idea. Unfortunately, this one teaches the wrong idea.

Rich

Abi Chapagai SSCrazy Points: 2666 More actions · Answer 12

Abi Chapagai

SSCrazy

Points: 2666

December 21, 2010 at 6:50 am

#1265036

Thank you for the good question.

pavanr SSC Veteran Points: 215 More actions · Answer 13

I agree that without an ORDER BY clause, the returned result has no meaning.

but can anyone explain, here in the case of Clustered Index, why it picks third record ?

Does this depend on Order of Insertion ie., Identity values ?