You don’t really need ORDER BY do you?
8November 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';
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.
[…] 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 […]
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)
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.
Honestly it’s been so long since I worked in Foxpro I don’t remember how indexes were handled. Can you give me a better idea what you mean?
[…] 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 […]
[…] 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 […]
[…] far so good. Unfortunately this way everyone ended up with Math 101 since, even though there isn’t an order specified it’s still most likely to pull in the order the rows were inserted. So let’s try […]