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

If you want the data to come out consistently ordered, yes - you have to use ORDER BY.

Answer 2

In my experience with SQL, most of the time I do not specify a ORDER BY in SQL, because the record sets are displayed in a "client-side" grid type control etc. where dynamic sorting is supported - in this case ordering by SQL is needless as it will be checked client side anyway.

This is also done client side because the same query might be used to display the data in different places in different orders.

Therefore it is only best practice to put in an ORDER BY, when

• The order of the data IS important; and
• The sorting is more efficient at the DB level.

i.e. if the front end developer is going to be "re-sorting" it anyway, then there is no point, as it unlikely to save overall processing time.

Answer 3

No serious RDBMS guarantees any order unless you specify an explicit ORDER BY.

Anything else is just pure luck or anectodal - if you want order, you have to specify ORDER BY - no way around that.

Answer 4

If you want the data ordered, the only way to guarantee anything (with every major RDBMS system that I'm aware of, definitely Sql Server and Oracle) is to include an ORDER BY clause. FIFO has absolutely nothing to do with the order data is returned without an ORDER BY clause, and there isn't a concept of any kind of DEFAULT sort order. The so called DEFAULT sort order is basically however the engine gets the data, which could be in literally any order based on indexes, cached data, simultaneous executing queries, load on the server, etc., etc.

This other stackoverflow thread is basically covering the same concept in relation to Sql Server, AlexK blogged a repo to demonstrate the behavior.