Stop SQL Server from ordering results? [duplicate]

Question

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?

Answer 1

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.

Answer 2

_{Community wiki answer:}

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

This is a demo I like:

CREATE TABLE dbo.Example
(
    [data]  integer NOT NULL,
    padding character(8000) NOT NULL DEFAULT ''
);
GO
-- Add 50 rows with [data] numbered from 1 to 50
INSERT dbo.Example 
    ([data]) 
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.number BETWEEN 1 AND 50
AND SV.[type] = N'P';
GO
-- Add a nonclustered primary key on [data]
ALTER TABLE dbo.Example
ADD CONSTRAINT [PK dbo.Example data]
PRIMARY KEY NONCLUSTERED ([data]);

The following query starts with a cold cache, and is forced to use the PK:

-- Flush dirty pages to disk
CHECKPOINT;
GO
-- Drop clean buffers from memory
DBCC DROPCLEANBUFFERS;
GO
-- Query forced to use the PK ordered by [data]
SELECT TOP (15)
    E.[data],
    E.padding
FROM dbo.Example AS E 
    WITH (INDEX([PK dbo.Example data]))
WHERE
    E.[data] > 0;

The execution plan shows the index being used in a seek:

We might expect rows ordered by [data] but the output typically varies with each execution:

The reason being that the nested loops join uses unordered prefetching.

---

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.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

• sorting (in a,b order) ;or
• sorting in b,a order ;or
• hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

Answer 3

MS SQL has the inclination to order results even if I don’t want it to.

By default, data appears to be ordered in primary key order, even if the primary key is nonclustered.

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

That is wrong from the first to the last statement.

Unless you specify ORDER BY in your SELECT server orders nothing.

What you see instead is the order in which it simply reads the data.

Disk-based tables in SQL Server come in 2 flavours: heaps and clustered tables.

In case of a heap the table can be read only in Allocation Order (using IAM pages) and in case of a clustered table it can be read in clustered index order or Allocation order

Here is my repro that shows what I've said + it demostrates that "data appears to be ordered in primary key order, even if the primary key is nonclustered" is wrong: clustered index order as the name implies is related to clustered index and NOT to primary key.

I have a clustered table dbo.Nums of natural numbers 1..1000000, now I use it to create another clustered table dbo.Nums2 that will have non-clustered PK but it will never be sorted in its order because I'll create a clustered index on it (that will not be PK) and as I already said, the ways to read this table are CLUSTERED INDEX ORDER or ALLOCATION ORDER only:

-- create table dbo.Nums2 with 3 columns:
select n, 1000000 - n + 1 as n2, replicate('a', 200) as filler
into dbo.Nums2
from dbo.nums;

-- create clustered index on it + non-clustered Primary Key:
create unique clustered index ix2 on dbo.Nums2 (n2);
alter table dbo.nums2 add constraint PK_nums2_n primary key(n);  

-- clustered index scan demonstration
select *
from dbo.nums2;

-- allocation order scan demonstration:
select *
from dbo.nums2 with (nolock);

Here you can see what was said above: clustered table can be read in 2 ways: in clustered index order (1st case) or in allocation ordrer (2nd case).

You can achieve allocation order scan under some different conditions, one of which I demostrated: my query runs under Read Uncommitted isolation level + my table has more than 64 pages.

Returning to your question.

Here are the plans produced by my queries: none of them has SORT operator. They have Clustered Index Scan with Ordered = False:

To recap:

• Data doesn't appear to be ordered, it appears as it was read by server

• Data doesn't appear to be ordered in primary key order, even if the primary key is nonclustered. It has nothing to do with PK defined. For clustered table the possible scan orders are CLUSTERED INDEX ORDER and ALLOCATION ORDER

• There is no extra unnecessary step which wastes processing time. Server does not sort data if it was asked to only return data in some way

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

Is there a way to get MSSQL to stop ordering data?

It DOES NOT order data.

If you want to demostrate it, the simplest way is to use a heap. Heap can be read only usin Allocation Order scan. So just enter the values in unordered way;

declare @t table (id int);
insert into @t values (1), (20), (3), (40), (5), (60), (7), (80);

select *
from @t;

I think I’m starting to understand: the data in the table is already in allocation order, and if I happen to use IDENTITY then that allocation order is also in sort order

That is wrong. Here is the example: my table has an identity column however allocation order has nothin to do with identity:

declare @t table (id int identity);
insert into @t default values; -- 1
insert into @t default values; -- 2
insert into @t default values; -- 3
delete @t where id in (2, 3);
insert into @t default values; -- 4
insert into @t default values; -- 5
insert into @t default values; -- 6
delete @t where id = 6;
insert into @t default values; -- 7
delete @t where id = 1;
insert into @t default values; -- 8
insert into @t default values; -- 9
delete @t where id = 1;
insert into @t default values; -- 10

select *
from @t;