You see dozens of blog posts and articles about how the order of a result set is not guaranteed without an ORDER BY clause and here is my contribution to the subject.

You all know I love examples so without further ado here we go:

Using data from AdventureWorks2014

-- Create and load the test table
SELECT AddressId, AddressLine1, AddressLine2, City, 
StateProvinceCode, PostalCode, SpatialLocation, 
Addr.rowguid, Addr.ModifiedDate
INTO OrderByTest
FROM Person.Address Addr
JOIN Person.StateProvince StateP
ON Addr.StateProvinceID = StateP.StateProvinceID;
-- Create a clustered PK and a non-clustered index
ALTER TABLE OrderByTest ADD CONSTRAINT pk_OrderByTest 
PRIMARY KEY (AddressId);
CREATE INDEX nci_OrderByTest_State_Zip 
ON OrderByTest(StateProvinceCode, PostalCode);
GO

Now that we have some data we run a report query.

-- "Report" query
SELECT * FROM OrderByTest WHERE StateProvinceCode = 'TX';

This is great, our information is ordered by PostalCode. Exactly the way we wanted it. No need to put an ORDER BY clause in the query right? You can run it 100 times and it’s still going to give you the information in PostalCode order. (Well probably, remember that without ORDER BY the order is not actually guaranteed.)

Over the next few months we get an influx of customers from Texas.

-- Adding additional rows for Texas the easy way
-- The @Count piece keeps AddressID unique.
DECLARE @Count INT;
SELECT @Count = MAX(AddressID) FROM OrderByTest;
INSERT INTO OrderByTest
SELECT AddressId + @Count, AddressLine1, AddressLine2, City, 
'TX' StateProvinceCode, PostalCode, SpatialLocation, 
Addr.rowguid, Addr.ModifiedDate
FROM Person.Address Addr;

It’s time to run the report again.

-- "Report" query
SELECT * FROM OrderByTest WHERE StateProvinceCode = 'TX';

Hu? What happened, now instead of being ordered by PostalCode the data is ordered by AddressId.

Looking at the execution plans will help.

Execution Plan from the first run.

Execution Plan from the second run.

You can see that the execution plan changes. Initially there were not that many addresses (percentage wise) in Texas so SQL decided to use an index seek on the State/PostalCode index with a key lookup to get the additional data. Once there was a high percentage of addresses in Texas SQL decided it would be faster to just us a clustered index scan. Because of the change in plan the order of the output changed.

-- Clean up code
DROP TABLE OrderByTest;

Now this is just a demonstration of one possible way the order of the data could unexpectedly change. Hopefully it brings home just how important that ORDER BY clause is. Even if the execution plan were to stay the same there is no guarantee that the data order will remain the same without an ORDER BY clause.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, order by, sql statements, T-SQL