Should all queries where you expect a specific order, include an ORDER BY clause?

All we need is an easy explanation of the problem, so here it is.

In order to better understand the SQL Server query processor, I’ve been thinking about how the ORDER BY clause works, and how SQL Server provides results.

It appears SQL Server will provide results in exactly the same order for any given set of data that remains the same and never, ever changes. Once you introduce any kind of uncertainty such as developers changing something, the order of results can no longer be expected to be the same.

Simply seeing the results in the same order, each time you press F5 does not guarantee the results you expect.

Try this:

USE TempDB;
DROP TABLE TestOrderBy;
DROP TABLE TestOrderBy2;
CREATE TABLE TestOrderBy
(
    ID INT NOT NULL CONSTRAINT PK_TestOrderBy PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData varchar(255)
);
INSERT INTO TestOrderBy (SomeData) VALUES ('E');
INSERT INTO TestOrderBy (SomeData) VALUES ('D');
INSERT INTO TestOrderBy (SomeData) VALUES ('C');
INSERT INTO TestOrderBy (SomeData) VALUES ('B');
INSERT INTO TestOrderBy (SomeData) VALUES ('A');

CREATE TABLE TestOrderBy2
(
    ID INT NOT NULL CONSTRAINT PK_TestOrderBy2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData varchar(255)
);
INSERT INTO TestOrderBy2 (SomeData) VALUES ('E');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('D');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('C');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('B');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('A');

SELECT SomeData 
FROM TestOrderBy
UNION ALL
SELECT SomeData
FROM TestOrderBy2;

CREATE INDEX IX_TestOrderBy_SomeData ON TestOrderBy (SomeData);
CREATE INDEX IX_TestOrderBy2_SomeData ON TestOrderBy2 (SomeData);

SELECT SomeData 
FROM TestOrderBy
UNION ALL
SELECT SomeData
FROM TestOrderBy2;

The results:

enter image description here

As you see, adding a simple index on the fields selected in the query alters the order of the results.

From here on, I’m adding ORDER BY unless I truly don’t care.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I had a simpler repro in mind:

CREATE TABLE #x(z CHAR(1));
CREATE TABLE #y(z CHAR(1));

INSERT #x SELECT 'O';
INSERT #x SELECT 'R';
INSERT #x SELECT 'D';

INSERT #y SELECT 'E';
INSERT #y SELECT 'R';

SELECT z FROM #x
UNION ALL 
SELECT z FROM #y;

Results:

O
R
D
E
R

Now add an index:

CREATE CLUSTERED INDEX z ON #x(z);

SELECT z FROM #x
UNION ALL 
SELECT z FROM #y;

Results:

D -|
O -|- ordered based on the clustered index, not how originally inserted
R -|
E
R

This still produces the first three rows from #x and the last two rows from #y, so still doesn’t prove that SQL Server might return those entire queries in a different order than physically laid out in the query (just that you can’t rely on the ordering inside those sets). But clearly this mythical "observe an order once, and it will always be true" statement needs to be nipped.

Bottom line: do not rely on observed order. If you want a certain ordering, say so using ORDER BY.

Also, please read this post by Conor Cunningham, a pretty smart guy on the SQL team.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply