Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Well note that the first 5 rows are still from TestOrderBy and the last 5 rows are still from TestOrderBy2. (In reference to stackoverflow.com/questions/15766359/…) –  Aaron Bertrand Apr 5 '13 at 17:20
1  
Also you don't have to have manual human intervention in order for certain behaviors to change. "Observed order" can change with a service pack, hotfix, reboot, plan clearing, data change, statistics change, trace flag, etc. –  Aaron Bertrand Apr 5 '13 at 17:21
    
Definitely; I simply wanted to see if the addition of something as simple as an index would have the desired effect. Clearly it does. –  Max Vernon Apr 5 '13 at 17:21
    
Although if the requirement is to prove the order of the UNION's then perhaps not with this simple demo. –  Max Vernon Apr 5 '13 at 17:23
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.