How do database servers decide which order to return rows without any "order by" statements?

Question

Kind of a whimsical question, always something I've wondered about and I figure knowing why it does what it does might deepen my understanding a bit.

Let's say I do "SELECT TOP 10 * FROM TableName". In short timeframes, the same 10 rows come back, so it doesn't seem random. They weren't the first or last created. In my massive sample size of...one table, it isn't returning the min or max auto-incrementing primary key value.

I also figure the problem gets more complex when taking joins into account.

My database of choice is MSSQL, but I figure this might be an interesting question regardless of the platform.

Answer 1

If they're not ordered by the calling query, I believe they're just returned in the order they were read off disk. This may vary because of the types of joins used or the indexes that looked up the values.

You can see this if the table has a clustered index on it (and you're just selecting - a JOIN can re-order things) - a SELECT will return the rows in clustered-index-order, even without an ORDER BY clause.

Answer 2

If you do not supply an ORDER BY clause on a SELECT statement you will get rows back in arbitrary order.

The actual order is undefined, and depends on which blocks/records are already cached in memory, what order I/O is performed in, when threads in the database server are scheduled to run, and so on.

There's no rhyme or reason to the order and you should never base any expectations on what order rows will be in unless you supply an ORDER BY.

Answer 3

There is a very detailed explanation with examples here: http://sqlserverpedia.com/blog/sql-server-bloggers/its-the-natural-order-of-things-not/

Answer 4

"How do database servers decide which order to return rows without any “order by” statements?"

They simply do not take any "decision" with respect to ordering. They see the user doesn't care about ordering, and so they don't care either. And thus they simply go out to find the requested rows. The order in which they find them is normally the order in which you get them. That order depends on user-unpredictable things like the chosen physical access paths, ordering of physical records inside the database's physical files, etc. etc.

Don't let yourself be misled by the ordering as you get it, in the case where you didn't explicitly specify an ordering in your query. If you don't specify an ordering in your query, no ordering in the result set is guaranteed, even if in practice results seem to suggest that some ordering appears to be adhered to by the server.