default order of rows not honored

Question

default order of rows not honored

psteja2000

Mr or Mrs. 500

Points: 586
More actions
June 6, 2011 at 3:29 pm

#252273

I am not really a 'newbie' but I am sure the observed behavior in my case is due to some obvious setting I am missing.
I have a table with primary key clustered asc, also is the identity(1,1) column.
I have a int column in the table which I use in my where clause.
when I do a select * with no where clause, the result set is by pk asc.
when I do a select * where col =intvalue, sometimes I get the result set by pk desc and sometimes by pk asc.
May be the answer is : Always explicitly mention the order by clause, never take for granted.
But I have never seen this behavior before and wondering if someone can throw some light on this.

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

There is no such thing as a 'default order of rows'. If you do not specify ORDER BY, SQL is free to return the data in any order it likes. It'll be whatever order the last query operator in the execution plan leaves the data in.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nothing more to add to what Gail said. Just following up with support of what she said because it's correct.

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

psteja2000 Mr or Mrs. 500 Points: 586 More actions · Answer 3

Thank you all for the replies. I know order is not gauranteed, but I just have never seen order changing based on the value in where clause.

eg: .... where col = x1 consistently returns rows in descending order

while

where col=x2 consistently returns rows in ascending.

Couldn't figure out how x1,x2 etc can dictate the order of the rows returned.

I appreciate your time. Thanks.

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

Different execution plans so probably using indexes differently, so different ordering.

Seriously, without an order by there is nothing you can say about the order of the rows. It's however they end up at the end of the query. Look at the execution plans if you're interested in why the queries are run differently

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

psteja2000 Mr or Mrs. 500 Points: 586 More actions · Answer 5

Just wanted to add a follow up. Order is not 'gauranteed' ofcourse, but as far as I know, it has no reason to bring back results out of order than 'natural' order (over custered pk). This is in case of a 'simple' table, with just the primary key.

However, my behavior above (started all of a sudden, all queries and application logic which assumed the queries are naturally ordered began to show unintended results) was because I had to add a unique index over some columns.

Take away - ALWAYS specify an order by if you are going to depend on an order.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Not sure if I am adding anything to the discussion but..

I fully agree "default order" is never guaranteed without an ORDER BY statement. In previous versions, at least 6.5 (maybe 7) and before the records were usually returned in order by the clustered index so the concept of default order was sort of there. And sloppy code (including some of my own regretfully) depended on that behavior.

Gail is absolutely right, different plans can and will return different orders, and that is the correct behavior. If you want to KNOW the order use an ORDER BY.

CEWII

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

psteja2000 (6/16/2011)
...behavior above started all of a sudden...

Well, you were lucky for a long time and finally you run out of luck 🙂

If business specs ask for a known order or the returned rows, specify "order by"

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.