Here’s one that pops up regurarly. Something like:
“My views are no longer ordered in SQL Server 2005. I have ORDER BY in the view, but when I select from it, the rows are not returned according to the ORDER BY.”
Since I tend to post a reply often enough to above type of posts, I decided to put it in writing once and for all, so I can point to this blog post. (Laziness is a virtue 😉 .)
A view is by definition not sorted. A view is supposed to behave like a table (and thanks to that we have the same language to operate against views as we have to operate against tables). This is why ORDER BY is not by itself allowed in a view definition.
It is allowed to have ORDER BY if you also have TOP, but the purpose of ORDER BY is now to make sure that the correct rows are returned (5 most expensive books, for instance), not in any particular order.
At some point in time, the “workaround” to have TOP 100 PERCENT popped up. The thinking was that we now are allowed to have ORDER BY, which makes the view “sorted”. It doesn’t. However, SQL Server 2000’s optimizer wasn’t as smart as 2005’s optimizer, so for simple queries, you often found that data was returned according to your ORDER BY.
SQL Server 2005’s optimizer is smarter, and if it finds TOP 100 PERCENT and ORDER BY it realizes that both these operations doesn’t affect *which* rows to return, so both operations are removed from the execution plan. That is smart, since these doesn’t affect the data anyhow. Remeber that a view is by definition not sorted.
Some developers has relied on the 2000 behavior to create “sorted views”. This behavior was never documented, just a side effect of the execution plan, and was never guaranteed.
So what do we do? Well, same as when we run queries against a table. We have ORDER BY when we read data from the view!
Aside: The query builder which you can use when you create a view has a very strange behavior. It allow you to check a “sort” checkbox, and it will then add ORDER BY and TOP 100 PERCENT to the SELECT statement. According to above explanation, this is kind of silly, but I’m hoping that MS will tidy this up for the next version of SQL Server.