Default order of rows without order by clause

Question

Default order of rows without order by clause

Low Rider

SSCommitted

Points: 1502
More actions
May 21, 2006 at 5:24 am

#112918

Hi

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

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

lakusha SSCommitted Points: 1857 More actions · Answer 1

Short question

There is no default order of rows without an Order By clause. You may get the rows always sorted the same way, but it is NEVER garantied without the Order By. Even with ranking functions like ROW_NUMBER().

What usually happens is that the row ordering just happens to match the clustered index or a covering index. But is you change the index, you may cause problems to your applications if they rely on ordering.

Other factors can also change the order: different query plan, parallel processing (ooops! the production server is a 4 way box and the query plan is not the same), use of ranking functions, table partionning, view partionning, etc.

Bottom line: always use the Order By clause. This is true for SQL2k, SQL2k5, Oracle, DB2, etc and any true relational db. You should *never* rely on the physical order of the rows on disk. Removing ordering dependence was one of the primary goals when relational db were first designed.

"Three of the principal kinds of data dependencies which still need to be removed are: ordering dependencies, indexing dependencies and access path dependence"

and

"2) The ordering of rows is immaterial"

E.F. Codd, 1970, p. 377 & p.379

A relational Model of Data for Large Shared Data Banks

Marshall Smith SSCrazy Points: 2041 More actions · Answer 2

I would just like to tweak lakusha's otherwise excellent answer.

Always use the ORDER BY clause when you need the results ordered. If you don't need them in a particular order, such as in a subquery, ORDER BY can really put a drag on your performance.

lakusha SSCommitted Points: 1857 More actions · Answer 3

Good point. Developers never seem to realize that nothing is free. I have seem SELECT statements with an ORDER BY clause on 20 columns

Quite fun with a 700,000+ row report.

A very good book to understand performance impacts is SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer. It is good for MSSQL, Oracle, Sybase, etc. Having a general horse sense understanding is the best starting point. Most developers still believe there is some magic involved somewhere and that mathematics don't apply to servers.

Marshall Smith SSCrazy Points: 2041 More actions · Answer 4

Excellent book recommendation.

The similar horror story I remember hearing was a developer coming to a DBA, because the users were bitching about how slow the data entry was. The DBA poked around, and found that every column in the main table was indexed, some columns more than once. When the DBA went back to the developer, he just blinked, and said, "Well, yeah, that makes the reports lightning fast!"