Why do results from a SQL query not come back in the order I expect?

Question

If items are inserted in a table, and then I write a query, such as select * from table, why are the results not in the order that I expect?

Answer 1

The order of a query can be forced by using an 'Order by' Clause in the statement. A SQL Database does not actually understand what order you put things in, or store the data in a given order. This means that you need to tell SQL what order you want the items in. For instance:

Select * from Table
  order by column1 desc

Think about it like handing some stuff to your friend to hold - she will have all of it for you later, but she stores it someplace in the mean time. She may move it around while you are not looking to make room for something else, or may hand it back in the same order you gave it to her, but you didn't tell her to keep it in order, so she doesn't.

Databases need to be able to move things around in the background, so the way they are built does not intrinsically know about any order - you need to know the order when you give it to the database, so that you can put it back in the order you want later. The order clause allows SQL to impose an order on the data, but it doesn't remember or have one on its own.

Important point: Even when SQL returned the items in the correct order without an order by statement the last 1 million times, it does not guarantee that it will do so. Even if a clustered index exists on the table, the results are not guaranteed to be returned in the order you expect. Especially when SQL versions change, not explicitly using an order by clause can break programs that assume the query will be in the order they want!

Answer 2

It is a common misconception to expect results in the order that is inserted. Even when a clustered index is used, the result set may not be as expected. Only way to force an order is to use an "order by" clause. The reason that the order is different from what is expected may vary. The query may be executed in parallel and the result set may be merged or it may be due to the query optimization plan while trying to return the result set as fast as possible.