0

I have a simple table that stores the history of all the actions made on another table. Up until now we didn't make any special order by on this history table and just fetched the records.

We need to fetch these records in the history table in the order on which the action were performed on the main table. We didn't use any order by timestamp in our select statement.

It just happened that it always returned the results in the order they were inserted which was the order on which the actions were performed.

Does this always happen or is it just coincidence?

Alecu
  • 2,627
  • 3
  • 28
  • 51

1 Answers1

1

Without an ORDER BY clause, row order is never guaranteed.

Without an ORDER BY clause, the server will return records in whatever manner it chooses. This is often the value the records are saved on disk because that is most convenient, and it will generally add records to the end of the table in most designs. However, the order records are saved on disk is an implementation detail, not guaranteed query engine behavior. In most RDBMSs, as records are deleted and added, you will see that the order records are returned is relatively arbitrary.

If you have a CLUSTERED index in SQL Server, it will store records on disk in order according to the index to aid searches. If you have an IDENTITY() value which increments on each record and a CLUSTERED index on that column (a common configuration), the server will store records roughly in the order they are added, but this can be overridden (see IDENTITY_INSERT, DBCC CHECKIDENT), so there's no guarantee of that behavior. Any consistent behavior should be considered incidental to the design, not a feature. It's no guarantee. It should not be considered reliable. The server is doing what takes it the least amount of effort, not trying to be consistent with record order. Again, without an ORDER BY, the order of records returned is not guaranteed.

By definition and design of the relational model, the order of records in a database are not important. By default, records are not related to each other. Unless you as the developer somehow store information related to the desired order in the RDBMS, then there is no way to determine the order records are added.

If order is important, you must use ORDER BY. If you need to establish that order, you must do it yourself when choosing what data to store.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Off to a great start. But you should add in that even in scenarios where the data is physically in order and would _typically_ come back in that order without an `ORDER BY`, if the query uses parallelism then the rows can get out of order when the streams are merged. – Solomon Rutzky Aug 20 '15 at 18:14
  • @srutzky Haven't seen that one. I have seen it choose the "wrong" clustered index, though. Say I join TableA and TableB, and I expect the order to be according to the clustered index on TableA. The query engine, however, decided to use the clustered index on TableB, and so I get the results in an order I don't expect. I can also see having issues with partitioned tables. The point isn't really to enumerate all the reasons why you might not get the order you expect, though, it's that you shouldn't expect anything unless you ORDER BY. – Bacon Bits Aug 20 '15 at 18:22
  • Well, you had already started to enumerate reasons, so why not go for a more complete answer, including the scenarios you just mentioned? The more information that people have, the more they learn about SQL Server which can help make better decisions, right? Besides, by focusing on the clustering aspect and those variations, that opens the door for some to think that by not falling into those nuances that they are "safe" from this behavior, even though your first and last paragraphs are explicitly clear about needing an `ORDER BY`. People interpret things in funny ways ;-). – Solomon Rutzky Aug 20 '15 at 19:10
  • Clustered indexes are not guaranteed to be stored on disc in key order http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean/24470091#24470091. They are usually traversed in index key order but may be traversed in [physical allocation order](http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/11/09/when-can-allocation-order-scans-be-used.aspx) in some circumstances. As well as allocation ordered scans and parallelism the advanced scanning feature can also return rows out of index order. – Martin Smith Aug 20 '15 at 21:20