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»»»

SELECT TOP Expand / Collapse
Author
Message
Posted Tuesday, December 21, 2010 6:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:59 PM
Points: 894, Visits: 889
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
Post #1037656
Posted Tuesday, December 21, 2010 6:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:13 AM
Points: 2,917, Visits: 2,536
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
Post #1037663
Posted Tuesday, December 21, 2010 6:44 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 717, Visits: 3,037
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
Post #1037674
Posted Tuesday, December 21, 2010 6:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:36 PM
Points: 1,786, Visits: 1,116
Thank you for the good question.
Post #1037679
Posted Tuesday, December 21, 2010 7:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:34 AM
Points: 103, Visits: 90
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 ?
Post #1037704
Posted Tuesday, December 21, 2010 7:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668, Visits: 485
Nice question. If there was an extra option "It Depends", i would have selected this option as the correct answer.
Post #1037713
Posted Tuesday, December 21, 2010 7:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:19 AM
Points: 262, Visits: 919
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.

Post #1037716
Posted Tuesday, December 21, 2010 7:35 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 20,584, Visits: 9,624
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!
Post #1037720
Posted Tuesday, December 21, 2010 8: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
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.

Post #1037775
Posted Tuesday, December 21, 2010 8:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, 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.
Post #1037778
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse