You don’t really need ORDER BY do you?

7

November 19, 2014 by Kenneth Fisher

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';

OrderByTest1

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';

OrderByTest3

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.

OrderByTest2

Execution Plan from the second run.

OrderByTest4

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.

7 thoughts on “You don’t really need ORDER BY do you?

  1. […] Database Migration Windows Management Framework 5.0 Preview November 2014 is now available You don’t really need ORDER BY do you? – Added to the SQL Server T-SQL Curah November 2014 Cumulative Updates for SQL Server 2012 Cult of the Flying Spaghetti VLOOKUP […]

  2. Actually, if the data you are extracting is going into a report or a spreadsheet or the like, then you may not need an order by; it can be best to sort the resulting data in the tool that receives the data. However, that’s an aside on a good example of why you DO need an order by if you expect the query to return in order.

    • Thanks for the comment. 🙂 I agree that it can frequently be better to let the tool that receives the data sort it. But in that particular case you don’t care about or need ordered data so including the ORDER BY clause is not only unneeded its unwanted as it will slow your query down. (requires a sort)

  3. Gerardo Galvan says:

    Great article, Kenneth. I still have to learn a lot about indexes and constraints in SQL Server, and your article gave me a hint on how they work. I don’t know if it provides any advantage, but I wish SQL server handled ORDER and indexing the way Foxpro and xBASE languages did (SET INDEX TO 3, etc.) Just my opinion.

  4. […] a post that I’m not terribly impressed with that for whatever reason is really popular, and sometimes I’m see a post that impresses the heck out of me even though I wrote it. So that’s part of what I get back. A sense of […]

  5. […] to never put an ORDER BY in a view. The idea is that a view shouldn’t have an inherent order. Just like any other query. If you want the data from a view ordered then you query the view with an ORDER BY clause. In fact […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: