56

I was always of the impression that a query with no specified 'Order by' rule, would order this by the results by what was specified within your where clause.

For instance, my where clause states:

WHERE RESULTS_I_AM_SEARCHING_FOR IN
ITEM 1
ITEM 2
ITEM 3

I would have imagined that the results returned for items 1, 2 and 3 would be in the order specified in the where, however this is not the case. Does anyone know what order it sorts them in when not specified?

Thanks and sorry for the really basic question!

Damon

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dmoney
  • 861
  • 2
  • 16
  • 24
  • 8
    Basically, if you don't care enough to specify an ORDER BY clause, you shouldn't care about what order is chosen. If you do care about the resulting order, then tell SQL Server by adding an ORDER BY clause that matches what you expect. Note that an ORDER BY clause *can* make a query more expensive by introducing potentially inefficient sort operations. – Aaron Bertrand Nov 18 '13 at 14:45
  • 3
    Keep in mind that without an _explicit_ order specification that the order may change after changing indexes on the table, updates to SQL Server, ... . It's a nice way to leave a time bomb for the next victim chosen to maintain the code. – HABO Nov 18 '13 at 15:00

2 Answers2

71

If you don't specify an ORDER BY, then there is NO ORDER defined.

The results can be returned in an arbitrary order - and that might change over time, too.

There is no "natural order" or anything like that in a relational database (at least in all that I know of). The only way to get a reliable ordering is by explicitly specifying an ORDER BY clause.

Update: for those who still don't believe me - here's two excellent blog posts that illustrate this point (with code samples!) :

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Thank you very much, I appreciate your answer to a some what trvial question! – dmoney Nov 18 '13 at 14:40
  • 2
    @Muhammed Ali This was my first assumption but in this case there is a distinct lack of primary key's on the columns I have to use! Unfortunate but hey! – dmoney Nov 18 '13 at 15:37
  • 8
    @MuhammedAli: it might **look like that** - but without `ORDER BY`, **there is NO order** guaranteed. Period. Get used to it. – marc_s Nov 18 '13 at 16:06
  • 2
    Spot on. Love the article by Conor Cunningham. – Matthew Sharpe Feb 12 '16 at 13:34
  • The Alexander Kuznetsov link is no longer working –  Feb 23 '21 at 07:00
  • @a_horse_with_no_name: thanks - can't find any other place where Alexander might have published this - so unfortunately, looks like this one is gone forever ...... (too bad!) – marc_s Feb 23 '21 at 15:29
  • 1
    There is a copy on archive.org: https://web.archive.org/web/20130922121836/http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx –  Feb 23 '21 at 15:37
30

With SQL Server, if no ORDER BY is specified, the results are returned in the quickest way possible.

Therefore without an ORDER BY, make no assumptions about the order.

Curtis
  • 101,612
  • 66
  • 270
  • 352