SQL Server – Why ORDER BY Does Not Belong in a View

order-bysql server

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)

I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the SELECT statement querying the view.

But being relatively new to practical SQL and the usages of views, I would like to understand why this is done so by design. If I've followed the history correctly, this was once possible and was explicitly removed from SQL Server 2008 and so on (don't quote me on the exact version).

However, the best reason I can come up with as to why Microsoft removed this feature is because "a view is an unsorted collection of data".

I am assuming there is a good, logical reason as to why a View should be unsorted. Why can't a view just be a flattened out collection of data? Why specifically un-sorted? It doesn't seem that hard to come up with situations where (at least to me / IMHO) it seems perfectly intuitive to have a sorted view.

Best Answer

(Indexed views aside, of course.)

A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way that you do this - just like selecting from a table, which is also an unsorted collection of rows, by definition - is to include the order by on the outer query.

Also to give a little insight into the history. You could never put ORDER BY in a view, without also including TOP. And in this case the ORDER BY dictated which rows were included by TOP, not how they would be presented. It just so happened that in SQL Server 2000, if TOP was 100 PERCENT or {some number >= number of rows in the table}, the optimizer was fairly simplistic and it ended up producing a plan with a sort that matched the TOP/ORDER BY. But this behavior was never guaranteed or documented - it was just relied upon based on observation, which is a bad habit. When SQL Server 2005 came out, this behavior started "breaking" because of changes in the optimizer that led to different plans and operators being used - among other things, the TOP / ORDER BY would be ignored completely if it was TOP 100 PERCENT. Some customers complained about this so loudly that Microsoft issued a trace flag to reinstate the old behavior. I'm not going to tell you what the flag is because I don't want you to use it and I want to make sure that the intent is correct - if you want a predictable sort order, use ORDER BY on the outer query.

To summarize and just as much to clarify a point you made: Microsoft didn't remove anything. They made the product better, and as a side effect this undocumented, non-guaranteed behavior became less reliable. Overall, I think the product is better for it.

Related Solutions

Oracle – Why Do an ORDER BY for INSERTs AS SELECT?

It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes).

Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y) then the data is more likely to be in a contiguous set of blocks.

MySQL Index – Why Index is Not Used with ORDER BY?

David Spillett's answer is correct in all points, except for the "encouraging" suggestion.

Here's a way to not only encourage but (in almost all versions) force the optimizer to choose a plan that uses the wanted index to find the 50 rows - and only after that perform the join. It can't always be used but the FOREIGN KEY constraint assures that in this case the two queries will produce identical results.
I call this technique "first LIMIT, then JOIN":

SELECT     p.*, ps.*           
FROM       ( SELECT     *
             FROM       productStore 
             ORDER BY   storeTitle 
             LIMIT 50 
           ) ps 
    INNER JOIN product p 
        ON p.productUUID = ps.productUUID       
ORDER BY   ps.storeTitle ;

Best Answer

Related Solutions

Oracle – Why Do an ORDER BY for INSERTs AS SELECT?

MySQL Index – Why Index is Not Used with ORDER BY?

Related Question