Why does ORDER BY not belong in a View?

(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.


If a view was allowed to be sorted then what should be the order of the result here?

CREATE VIEW dbo.V1
AS
  SELECT number
  FROM   SomeTable
  ORDER  BY number ASC

GO

CREATE VIEW dbo.V2
AS
  SELECT number
  FROM   SomeTable
  ORDER  BY number DESC

GO

SELECT *
FROM   dbo.V1
       JOIN dbo.V2
         ON V1.number = V2.number 

one possibility is to avoid conflicting sorts- if the view is sorting by one order and the select on that view is sorting by another order (not being aware of the view sort), there may be performance hit. So it is safer to leave the sorting requirement to the user.

another reason, sort comes with a performance cost, so why penalizing all users of the view, when only some users need the sort..