When no 'Order by' is specified, what order does a query choose for your record set?

If you don't specify an ORDER BY, then there is NO ORDER defined.

The results can be returned in an arbitrary order - and that might change over time, too.

There is no "natural order" or anything like that in a relational database (at least in all that I know of). The only way to get a reliable ordering is by explicitly specifying an ORDER BY clause.

Update: for those who still don't believe me - here's two excellent blog posts that illustrate this point (with code samples!) :

• Conor Cunningham (Architect on the Core SQL Server Engine team): No Seatbelt - Expecting Order without ORDER BY
• Alexander Kuznetsov: Without ORDER BY, there is no default sort order (post in the Web Archive)

With SQL Server, if no ORDER BY is specified, the results are returned in the quickest way possible.

Therefore without an ORDER BY, make no assumptions about the order.

As it was already said you should never rely on the "default order" because it doesn't exist. Anyway if you still want to know some curious details about sql server implementation you can check this out:

http://exacthelp.blogspot.co.uk/2012/10/default-order-of-select-statement-in.html