SQL best practice to deal with default sort order

Question

A lot of SQL code I've read, it seems like the developer assumes that the default sort order always holds. For example when building an HTML select list they would just SELECT id, name FROM table without issuing an ORDER BY clause.

From my own experience it seems like dbms alway orders data using FIFO if no ORDER BY clause is given and no index. However, the order is not guaranteed. But I have never seen a dbms reordering data if there no change to the table.

Have you ever experienced a dbms selecting data in a non deterministic order if there is no change to the table?

Is it best practice to always put an ORDER BY clause?

Answer 1

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

Answer 2

As the other posters mention, if you don't specify a sort order, the SQL standard says the results can be in whatever order the query processor finds most expedient and efficient.

Let's say you do a simple unordered SELECT for all the rows of a CUSTOMER table, which has no indexes and no primary key. It's quite possible, and even likely, that the query processor will do a straight table scan and produce the rows in the order they were originally inserted (giving you the FIFO behavior you saw).

If you then add an index on the STATE and CITY fields (in that order), and then query for WHERE STATE = 'NY' the query processor may decide it's more efficient to scan the index entries for STATE = 'NY' rather than to do a full table scan. In this case it would probably materialize the rows in STATE, CITY order.

Even this is not certain. For example if the query processor has gathered statistics that show that nearly all the STATE values in your table are 'NY' (maybe because the database is for an Albany-based equipment rental business), it may decide that the table scan is actually cheaper than the index scan, and you'll see FIFO again.

It's a good idea to learn some basics about how your database plans its queries. You can use the EXPLAIN statement to see how your DBMS would execute any given query, and then use this to optimize your query, in some cases by orders of magnitude. This is a fascinating and useful area to learn.