Stop SQL Server from ordering results? I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server seems to return ordered results even when ORDER BY is not present in the query. This is a problem because I'm concerned that my students will become confused on this point.

Examples of observed behaviour:


By default, data appears to be ordered in primary key order, even if the primary key is nonclustered.
If I use GROUP BY, the result set appears to be in order of the last column.
For example GROUP BY this, that ends up with the data in order of that, which is weird, especially if there is no index on it.


I haven't observed this behavior in other databases such as MySQL, PostgreSQL and SQLite. Is there something fundamentally different about SQL Server which causes it to order results for some queries even without an ORDER BY? 

Now it seems to me that ordering the data when not asked is an extra unnecessary step which wastes processing time. Is that true?

And is there some kind of global setting that disables this behavior? Is there a way to get SQL Server to stop ordering data?

It also makes it harder to demonstrate to students that data is not automatically ordered.

Can anyone give me a simple demo script that shows the order of the results changing from execution to execution?
Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It's organized by last name, first name.

If I asked you to get that phone book and read out the names:

SELECT FirstName, LastName FROM dbo.PhoneBook


You would usually read them out to me in last name order. You wouldn't have to sort them - they'd just come out that way because that's how they were already stored.

That's why SQL Server's results can seem sorted by default.

Thing is, it's not guaranteed, and several scenarios can change that. For example, say you walk over to the phone book to read the last names for me, and someone else is already reading through the white pages. You might choose to follow along with them, reading from the same area of the phone book. You would start where they already were (say the M's), and then when that other person reached the end of the phone book (the Z's), you'd circle back to the A's and read the part that you'd missed. That's called merry-go-round scanning, and SQL Server Enterprise Edition does that by default, and you can't turn it off. You can't predict when you're going to get it, either.

Another scenario - say that I only ask you for the first names in our city, not the last names:

SELECT FirstName FROM dbo.PhoneBook


Note that I didn't ask for the names to be sorted. At first, when we only have the white pages, you might use those white pages and yell the answer out to me - but they wouldn't seem sorted. (Our phone book's white pages are ordered by last name, first name, so if you just read from there, the first names will be all over the place.)

If later, someone creates an index just on first name, SQL Server is smart enough to use that for my first-name-only query because it'd be the narrowest/smallest object available to satisfy my query. Suddenly, the results might seem sorted by first name - but that's just a coincidence because there's a new index available to satisfy my query.


  For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.


In order to do grouping, you may have to sort data. Taking our phone book example, if I asked you to group everyone together by first name, you would have to sort them by first name in order to accomplish that task. Note the resemblance is between the phone book and the index, not the table (except when the table has a clustered index, in which case the table is the clustered index, so the table/clustered index has a fixed logical order).



Now for some gentle career advice: because these concepts are new to you, I would humbly suggest that rather than forcing SQL Server to do your bidding, thinking that you're somehow going to tune it to go faster, that you take a few steps back. You're asking a great curiosity question here, so I'd suggest watching my free SQL Server training video series, How to Think Like the SQL Server Engine. I use pages from the Stack Overflow database to explain how SQL Server delivers your query results.

My suggestion might seem a little controversial - after all, I'm pitching my own training here - but it's free, and it's even open source, licensed under the MIT license for folks to reuse. After you've mastered it by watching me do it, you can take the slide deck and train your fellow teammates.