Query default sort order

Question

Post reply

Query default sort order

tsduke

Ten Centuries

Points: 1199
More actions
January 20, 2011 at 8:06 am

#243987

I thought I knew the answer to this until restoring a 2000 db in 2008 for migration testing. An identical query run on both servers returns the data in a different order. In 2008 it appears to be in order of the PK. In 2000, it's not. Now my first response to the developer is if you are concerned with the order the specify it, but this does have me curious.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

If ORDER by is not specified , no order can be guaranteed.

I blogged about this a little while back

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/06/27/sql-101-without-order-by-order-is-not-guaranteed.aspx

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Without an order by you can say nothing about the order of the returned result set. It'll depend on the plan the optimiser picks, the scan method the storage engine chose, etc. It can change from day to day.

Need an order? Specify ORDER BY.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tsduke Ten Centuries Points: 1199 More actions · Answer 3

CELKO (1/20/2011)
Tables have no ordering by definition and you have to impose one by using the ORDER BY clause in an explicit or implicit cursor. In fact, you cannot guarantee the same query on the same server will produce the same output order from one execution to the next. A change in the stats, the indexing or other sessions can all affect the results.
This is so basic to RDBMS, I think you did to do some reading.

Wow, I think you are bit harsh.

Maybe I didn't elaborate enough in my initial post. Like I said I knew the answer to this, but testing ALWAYS returns the same sort each time on it's respective server. No matter how many time stats are updated, indexes are rebuilt, etc. So the results in this case were not what I expected when explaining to the dev. Before posting here I was giving the developer the same anwers you all are confirming here, but was wondering why server1 always returns the same sort and server always returns it's same sort. I would expect a different result over time, but not seeing such.