1

So I was writing some queries today and was using top 10 on a query just to get some sample data without an orderby and noticed it was actually returning different data on each call. It did not seem random however as it is a table with millions of rows and some entries always showed up, while others changed every call. What logic goes on in the back end to decide which rows to return when no orderby is specified? I am using SQL Server 2008r2, but I'd be interested if it is handled differently in other DBs.

Kevin DiTraglia
  • 25,746
  • 19
  • 92
  • 138
  • 3
    If you don't have an order-by, the DBMS is free to return whatever order it wants - I guess the ordering will be a function of whatever sorting and joining it needs to do to get your results. It's not documented afaik. – Blorgbeard Jun 14 '12 at 21:27
  • The DBMS can do whatever it wants, as long as it satisfies your restrictions, including (lack of) ordering. It might return the rows that are currently present in the buffers (does MSSQL use versioning? IIRC it does) – wildplasser Jun 14 '12 at 21:29
  • There are many questions about this topic here. You can look at this comment made a couple hours ago for SQL Server http://stackoverflow.com/questions/11040874/sum-of-differencies-of-rows#comment14440602_11040874. Or this post http://stackoverflow.com/questions/5061595/select-bottom-rows-in-natural-order. Or many others. – Lamak Jun 14 '12 at 21:31

1 Answers1

2

Of course the order is entirely undefined. But I'll try to mention some actual causes in real implementations because this was explicitly asked for. Here is a small sample:

  • parallel query plan
  • read-ahead results returning in random order
  • the execution plan changed for some reason
  • read of a column store index returns rows in internal order
  • SQL Server is choosing an unordered index scan which is reading the pages in allocation order and not in logical order (the allocation order can change by reorganizing the table for example)

These causes are specific for SQL Server.

usr
  • 168,620
  • 35
  • 240
  • 369