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


SELECT TOP


SELECT TOP

Author
Message
ronmoses
ronmoses
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1023
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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7403 Visits: 2913
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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5789 Visits: 3691
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2058 Visits: 1127
Thank you for the good question.
pavanr
pavanr
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 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
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: 1414 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 Guru
SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)

Group: General Forum Members
Points: 156048 Visits: 9672
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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4666 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.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: 1784 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