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 hold. 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 order data using FIFO if no ORDER BY clause is given and no index. However, the order it is not guarantee. But I have never seen a dbms reordering data if there no change to the table.

Have you ever experience 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

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

Answer 3

Yes. There is no "default order" without an ORDER BY, and there's no guarantee that you'll get the data back in FIFO/LIFO or any other order.

As far as the developers using "SELECT id, name FROM table", they're either inept or they don't care what order anything appears in.

Answer 4

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 5

Perhaps the writers of those SQL queries you're reading don't care about the order of the data returned. The best practice is to use it where you need to ensure the order of the results returned!

Answer 6

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.

Answer 7

Even a simple query like SELECT ... FROM table can return data in various order. I know this to be true in theory, I know this to be true in practice, and I have seen plenty of cases when the order changes between subsequent executions, even when no data change occurs in the table.

A typical example of order changes between executions is when the query is executed using a parallel plan. Since parallel operators return data as the underlying threads produce it, the order of the rows in the result varies between each run. This situation makes even the simple SELECT in your example return wildly different results each time is run.

Answer 8

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.