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:
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.
Best Answer
I had a simpler repro in mind:
Results:
Now add an index:
Results:
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.