Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT TOP


SELECT TOP

Author
Message
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
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
sjimmo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3345 Visits: 2824
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
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
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
Abi Chapagai
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: 1812 Visits: 1127
Thank you for the good question.
pavanr
pavanr
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 96
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 ?
Surii
Surii
Say Hey Kid
Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

Group: General Forum Members
Points: 697 Visits: 489
Nice question. If there was an extra option "It Depends", i would have selected this option as the correct answer.
Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 944
ronmoses (12/21/2010)
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.


I read through all the comments to be sure the "order by" was already.. noted. (as if there would be anything for me to add after the first 10 posts)

I don't have time to make a reproducible illustration but wanted to throw in these two cents: With large data sets and parallelization you can get apparently unordered results even with the clustered index in effect due to the merge step simply combining multiple streams. Without an explicit order by clause, you get the unsorted merged streams. I discovered this trying to remove the cost of the order by under the erroneous assumption that the clustered index made the order by unnecessary. No, it's very necessary.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Mike Dougherty-384281 (12/21/2010)
ronmoses (12/21/2010)
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.


I read through all the comments to be sure the "order by" was already.. noted. (as if there would be anything for me to add after the first 10 posts)

I don't have time to make a reproducible illustration but wanted to throw in these two cents: With large data sets and parallelization you can get apparently unordered results even with the clustered index in effect due to the merge step simply combining multiple streams. Without an explicit order by clause, you get the unsorted merged streams. I discovered this trying to remove the cost of the order by under the erroneous assumption that the clustered index made the order by unnecessary. No, it's very necessary.




So which was better performance wise? maxdop 1 or 0?

Yes I know the results are wrong without the order by!
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1412 Visits: 1046
Good Question... I agree that not having an order by statement can cause the output to change. I also agree that the clustered index created in the script also had this affect.

I also agree that this table schema would need a re-write for a production transactional database project. Thier are several articles in BOL and other places that would point that out.

1. A table identity column should be part of the primary key for the table.
2. If a table has a clustered index, it should include the primary key.
Dietmar Weickert
Dietmar Weickert
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1608 Visits: 374
BOL (http://msdn.microsoft.com/en-us/library/ms189463.aspx) states clearly
If the query has no ORDER BY clause, the order of the rows is arbitrary.
hence putting "Ann" as the correct answer is simply wrong.

Nevertheless, using TOP without an ORDER BY clause may definitely be useful: I like to use it to see just a sample row of a table not well known to me...;-)

Best regards,
Dietmar Weickert.
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