SQL Server: Order of Rows

We all know that a simple statement such as:

SELECT * FROM stuff;

should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order.

Moreover a statement such as:

SELECT thing,whatever FROM stuff
GROUP BY thing,whatever;

always appears to sort thing by the last field in the GROUP BY clause, which is not at all helpful.

The question is, under what circumstances does SQL SERVER order the result even if unasked, and what can I do to stop this?

I am trying to demonstrate to my students that order is indeterminate unless specified, but this is not helping my case.

I admit that I am working with a small set of sample data.

Thanks

This article by Conor Cunningham (Software Architect, SQL Server Engine at Microsoft) should answer your questions:

• No Seatbelt – Expecting Order without ORDER BY

Some extracts below:

The hard part here is that there is no reasonable way for any external user to know when a plan will change . The space of all plans is huge and hurts your head to ponder. SQL Server's optimizer will change plans, even for simple queries, if enough of the parameters change. You may get lucky and not have a plan change, or you can just not think about this problem and add an ORDER BY.

[...]

There are lots of situations where plans can change in the Optimizer - for more complex queries, there can be thousands of plan choices or more, and each of them has a case when it would likely be picked. For each of those plans, the sort for that plan can be different if you don't specify it.

So, my advice for the day:

If you need order in your query results, put in an ORDER BY. It's that simple. Anything else is like riding in a car without a seatbelt.

StackExchange Database Administrators Q#146745, answer score: 9