This is mainly a reference post for myself as it took a few tries to get correct Google queries to find the answers I’m after.
Question:
What is the natural/default sort order of records returned from a query on a SQL Server table (where an ORDER BY is not explicitly specified)?
Answer:
There is no guarantee of the sort order.
Often it will return records in the order you inserted them, sometimes it won’t.
If you want to guarantee an order for the returned records, provide an ORDER BY clause in the query.
Resources:
- Google: “what is the default ordering of rows returned from a select query?”
- https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql/6064
- https://dba.stackexchange.com/questions/5774/why-is-ssms-inserting-new-rows-at-the-top-of-a-table-not-the-bottom/5775#5775
- https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629
- Google: “sql server query natural sort without order site:stackoverflow.com”
- https://stackoverflow.com/questions/26236352/default-row-order-in-select-query-sql-server-2008-vs-sql-2012
- https://stackoverflow.com/questions/17345731/return-rows-in-the-exact-order-they-were-inserted
https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/
Page no longer exists. See in in the Web Archive’s Wayback Machine: