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.

  • 2
    The first answer is spot on perfect. I would suggest if you wish to order a view why don't you just do this. Select [Columns] From [YourView] Order By [Columns]
    – Zane
    Commented Jul 25, 2012 at 18:34
  • Short answer: "For the same reason that ORDER BY does not belong to a table." Commented Dec 22, 2018 at 17:12

7 Answers 7


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

  • Here's mentioned(in a comment) that the TOP performs a cursor operation over the result set. Therefor it can have an explicit order. Commented Jul 24, 2012 at 20:46
  • @TimSchmelter that doesn't help when the optimizer sees TOP 100 PERCENT / ORDER BY and removes it from the plan completely. Try it out. Commented Jul 24, 2012 at 20:47
  • It was just a sidenote. The rest of the comment is as follows anyway: "the SELECT TOP x trick is scheduled to be deprecated in a future version of SQL Server, so it would be best to not to use it at all." Commented Jul 24, 2012 at 20:51
  • @TimSchmelter it's already a no-op. I don't think it will stop working in any new version any time soon, because it will break too much existing code. Commented Jul 24, 2012 at 20:52
  • 2
    @TimSchmelter there order is not talking about the order of the rows, it is talking about the order of the columns within a row. In SQL Server we generally don't talk about a row being a tuple because the physical implementation of a row is so obvious to us (the table lists the columns in the order you defined them). Commented Jul 24, 2012 at 21:07

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

  SELECT number
  FROM   SomeTable
  ORDER  BY number ASC


  SELECT number
  FROM   SomeTable
  ORDER  BY number DESC


FROM   dbo.V1
       JOIN dbo.V2
         ON V1.number = V2.number 
  • I don't get this point. Instead of writing database view, you can write normal sql query. So what should be the order of the result in that case ? thanks
    – hqt
    Commented Dec 19, 2016 at 18:50

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


ANSI SQL only allows the ORDER BY on the outermost query for a variety of reasons, one being what happens when a subselect/view/CTE is joined to another table and the outer query has an ORDER BY itself.

SQL server never supported it inside a view (unless you tricked it using a TOP 100 PERCENT which in my opinion is mostly triggering a bug).

Even if you triggered the bug, the results have never been reliable, and sorting didn't always come out the way you expected.

See this blog post by the the Query Optimizer team for a complete technical explanation TOP 100 Percent ORDER BY Considered Harmful.

The default plan implementation for this code happens to sort the rows as part of performing the TOP operation. Often this meant that the results happened to be returned in sorted order, and this led customers to believe that there was a guarantee that rows were sorted. This is actually not the case. If you want rows to be returned to the user in sorted order, you need to use an ORDER BY on the outermost query block (per ANSI) to guarantee the output presentation order.


Views behave like tables whose contents are determined by the results of a query.

Tables don't have order; they're just bags of rows.

Therefore, views don't have order either. You can sort them by selecting rows in a particular ORDER, though.

  • 1
    Tables... are just bags of rows - and then viwes are just virtual bags of virtual rows - views "don't exist" - e.g. there's no data stored for them at all - they're just "stored definitions of a query to be executed", basically.
    – marc_s
    Commented Jul 24, 2012 at 20:36
  • 1
    +1 The equivalency of tabels and views seems to me the main reason why views should not contain an "order by"
    – miracle173
    Commented Jul 25, 2012 at 7:34
  • 1
    "Views behave like tables". I would say that "Views behave like base tables. Views are tables." Commented Jul 31, 2012 at 0:49

One answer not yet given is that "order by" can interfere with predicate pushing which can greatly affect performance.

An example is having a view that rolls up a large set of data into a 10 line summary that is top 10/Ordered:

select * from TopOrderedView; -- Ordered 10 line summary in 5s

For the same case with a strong predicate:

select * from TopOrderedView where <condition>; -- Ordered 2 line summary in still 5s

It still takes the same amount of time because the top/order by is blocking the predicate from being run earlier in the pipeline. This may cause unneeded rows to be processed and the plan will be similar to the one without a predicate.

Whether the push should happen before the order by is really a developer choice and can change the answer. Most often the push is the logical intent.

Using "top 100 percent" logically allows predicate pushing however the implementation unfortunately ignores "order by" for this case and defeats the intent.

For real use cases, a nice compromise would be for the engine to perform "order by pulling." This would allow an "order by" to be specified within the view (with top 100 percent or no top) and that order is only used if the view is selected from directly, without an explicit order by, no joins, no aggregates, no view chaining etc. Both cases listed above could be supported by this simple model.


you can create a view which has order by and preserves the order by when queried after:

select top 99.999999999999 percent * from ..... order by

  • 1
    Why not just SELECT TOP 100 PERCENT ... ?
    – Hannah Vernon
    Commented Dec 16, 2015 at 21:08
  • 2
    @Max probably similar to this trick - that doesn't make it a good idea. Commented Dec 16, 2015 at 21:30
  • Agreed, @AaronBertrand - just noting there is no need to use 99.99999999999 :-)
    – Hannah Vernon
    Commented Dec 16, 2015 at 22:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.