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 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.