A long, long time ago in a galaxy you can reach out and touch right now, ORDER BY in a view meant something. If you had a view like the following:

CREATE VIEW dbo.Rubbish
AS
  SELECT TOP 100 PERCENT Trash
    FROM dbo.Garbage
    ORDER BY Trash;

And then you ran a query such as:

SELECT Trash FROM dbo.Rubbish;

You could reliably expect the rows to come back ordered by Trash in ascending order, even though it is relatively clear that the ORDER BY here serves a completely different and overloaded purpose – to dictate which rows should be filtered by TOP.

When SQL Server 2005 came around, changes to the optimizer meant that this behavior – which was never documented, by the way, hence never guaranteed – ceased to work as reliably as it had in previous versions. In fact in this case the optimizer sees TOP 100 PERCENT and simply throws out both TOP and ORDER BY. Compare the differences in the plan from the view with and without the ORDER BY:'

Notice how there is no sort operator in the query without ORDER BY? Because we didn't tell SQL Server what order we want, it's free to make its own choice. In this case it is choosing a clustered index scan which is not based on the Trash column. You can see the end result of the difference in these plans by comparing the results from the select:

Obviously only the latter result honors the outer ORDER BY, and in fact both cases have discarded the inner TOP/ORDER BY combination.

People are surprised by this change, and in fact variations on the symptom come up in forum discussions all the time. Further to the confusion, that still exists today, early on there were some very vocal customers who screamed bloody murder and were very stubborn about changing their code to put the ORDER BY on the outermost query where it belongs. After upgrading to SQL Server 2005 they cried foul until Microsoft finally submitted – creating a trace flag that forced the optimizer to behave the old way. Note that this trace flag is only valid in SQL Server 2005 and SQL Server 2008 – in SQL Server 2008 R2 and SQL Server 2012, the trace flag seems to be a no-op (at least in every case that I've tried). Even if you are still on 2005/2008, I highly recommend using this option as a last resort only, because it changes the way the optimizer works and this can have other undesirable impacts on your entire workload.

Hopefully none of this is news, as it has been discussed at length both online in general, and more specifically on Connect. Where it gets more interesting is that there have been several bugs reported that tools within Management Studio, such as the view designer, actually encourage this behavior and help perpetuate the myth. The issue I noticed this weekend is that, while most bugs have been closed as won't fix, one (#249248) has been closed as fixed. So I immediately fired up the latest build of Management Studio I have, right-clicked the views node, and selected New View. Once I added a table, what I saw was not very promising, as I was able to choose Sort Order and Sort Type:

So Management Studio for SQL Server 2012 still allows me to specify a sort order and sort direction for any column in the view! And what does it do to the SQL? It injects TOP 100 PERCENT / ORDER BY! Bad, bad, bad!

On the plus side, if I try to *save* this view, I at least get some kind of warning:

It's great that they're finally trying to stop people from believing that ORDER BY in a view means anything about ordering the result. But the problem that's being overlooked here is that not everyone uses the view designer to create and save a view in a single session. They may be generating code for a view that they're going to cut & paste into a CREATE/ALTER view script elsewhere, to use in a CTE or derived query in a more complicated script, or to send to a colleague. In a lot of cases they are never going to see this warning, because this won't magically pop up when they try to use the syntax in other scenarios.So I left a comment on the Connect item with a little detail about that, because I'm unfortunately going to have to disagree that this issue has been "fixed." I'm not sure why the Sort options in the view designer can't just be hidden without changing the behavior of the tool in general – can't it just always behave as if I hadn't changed those options at all?

I do acknowledge that there are some edge cases here, where someone really does want to create a view that selects the top (n) rows ordered by some criteria. So perhaps my suggestion to remove the columns altogether is a bit harsh. But when I choose a Sort Order for one or more columns, it should not just throw TOP 100 PERCENT into my query; the query that's produced at this point shouldn't be valid. Instead it should prompt me with a similar warning to what I get now when I click Save, with additional wording that implies, "If you really do mean to take some subset of the rows in the table, please add a TOP (n) clause to indicate the number or percentage of rows you want to return."