Home > View Post

Guaranteeing the order of results in SQL Server

It's a common misconception that SQL Server will return data in the order of the clustered index in the absence of an ORDER BY clause. So common that many developers have come to rely upon this fact and, whilst this assumption might seem to reliable, it is far from guaranteed.

So let's be 100% clear on this: if you need your data in a certain order then specify an ORDER BY clause. If you don't, the one time the data doesn't come back in the same order you've seen a billion times before will be when you least expect it. For example, when you roll out your heavily tested system to live!

Why is this?

David Portas talks about this in his post The Facts About Clustered Indexes:

"A query against a clustered table won't always scan the clustered index. Even when it does, the scan isn't necessarily ordered. One reason is that the query may be executed in parallel over multiple processors which then merge their results. Another is that the optimizer may choose to perform an unordered scan using the Index Allocation Map rather than scanning the leaf pages in order."

Pinal Dave talks about it in his post Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used

"SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest. In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index."

"There are few incidence I have observed that, when parallelism is used to return the query results, many times due to different speed and work load on different CPU the resultset is not according to clustered index. SQL Server Parametrization and SQL Server cache can return the results which are not built by clustered index."

However, I was worried about the lack of Microsoft documentation on this matter and contacted Conor Cunningham who's an Architect on the SQL Server Core Engine. Fortunately, Conor has responded with a great post. Surely the case is closed on this one: use an ORDER BY!

Check out his post No Seatbelt - Expecting Order without ORDER BY.

He even provides a walkthrough example that proves the point. Thanks Conor.

Tags: ASP.NET

 
Josh Post By Josh Twist
8:34 AM
29 Aug 2008

» Next Post: BizTalk 2006 R2 in no one-way support for WCF shocker
« Previous Post: Explore Stack Trace in Resharper

Comments are closed for this post.

© 2005 - 2017 Josh Twist - All Rights Reserved.