«

»

Dec 21

Assuming order of data without ORDER BY

Why are my results out of order?

Many times I have seen posts in forums asking about why their data is not being returned in the order of the clustered index. Similar questions have been brought up questioning the order of data from a view when the view includes an ORDER BY clause (which it does not really allow except in a specific case). The fact is that you are NEVER… Let me repeat that… NEVER guaranteed the order of your result set without the ORDER BY clause in the main query. I am not going to get into the details of “why” in this post as it can be quite detailed and confusing when you get down to how rows of data are stored on an individual page and how SQL Server retrieves the data. What I WILL say is that when SQL Server returns results, it does so in the way that it determines most efficient at run time. This is many times in the order of a clustered index or the order of a view or subquery, but not always. When your applications or BI depend on the order of this data, you can not afford to rely on luck.

I have had people tell me that “This view has returned in the proper order every time since we scripted it.  Why is is all of the sudden returning a different order? You must have done something to the database or the view.” Let me ask you this, if I show you a quarter and display both the heads and tails side to you, then flip it 100 times and it comes up heads 100 out of 100 times, do you now assume the quarter will come up heads 100% of the time? I hope not…

Below are 3 situations that demonstrate the wrong way of assuming order of results. Now, these may return the results in the same order each time you test them. However, SQL Server may someday decide that it is more efficient to return those in another way and you will get a different order.

No ORDER BY in query

Many people assume that the result set will always be returned in the order of the clustered index when an ORDER BY is not present in the query. This is not true. You can try to prove me wrong by running a query 100 times and showing me the result set, but then I refer you to my quarter. In order to guarantee your results you will have to run the query below:

ORDER BY in subquery

This first one is a simple subquery using an ORDER BY, but not the main query. This will not guarantee the order of results will be in the same order as the subquery:

To fix this you would change the query to:

ORDER BY on a view

First of all, SQL Server will give you an error if you try to use an ORDER BY without a TOP clause:

Msg 1033, Level 15, State 1, Procedure viewName, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I can only assume this is because SQL Server is trying to tell you that an ORDER BY in a view does not guarantee you order of results. I have seen developers ignore the purpose of this error and do the following:

This will compile and create the view, but when you SELECT from this view without an ORDER BY, you will not be guaranteed the order of results. Why does SQL Server let you compile the view in this case? Lets say you have 100 students and would like the view to only return the top 10 in regards to their grades. Using a TOP 10 with the ORDER BY will sort the results of the query, and return the top 10.  However, the top 10 will not be guaranteed to be in any particular order.

Summary

When you need to guarantee the order of your result set, always make sure that the ORDER BY is present and is part of the main query. Never assume the order of your result set without this.

Leave a Reply