TOP and ORDER BY operators in SQL 2005/8

Changes to the TSQL language beginning with SQL Server 2005 can change the order of results returned from a query selecting from a view. That in turn, has the potential to break some application functionality. Most sites will be unaffected, but some may find that certain code is broken. If your application depends on sorting results from a view defined as:

SELECT TOP 100 PERCENT…
…
…ORDER BY THISCOLUMN

You might be affected by this change.

Simply put, a view created with an ORDER BY clause is no longer guaranteed to return rows in the specified order. In order to guarantee the order of the result set In versions newer than SQL Server 2000 you must sort the result set in the query that selects from the view, not in the view definition.

If you are planning an upgrade from SQL 2000, you certainly want to be aware of this so you can test your existing code. If you have already upgraded you may want to make sure that subtle changes in application functionality have not gone unnoticed.

Views that are created with a TOP operator and an ORDER BY clause are still guaranteed to return the same rows, but the rows are not guaranteed to by in the sequence defined by the ORDER BY clause. In most cases the order will be correct, but not in all cases.

This is not a bug, it is a deliberate change made for the purpose of bringing TSQL into closer compliance with the ANSI SQL language standard. Views were never intended to return sorted results. But, because it was possible in previous versions of SQL Server, there is a significant amount of code in productions systems that will be affected by this change. Microsoft has a hotfix for later SQL versions which restores default SQL 2000 behavior. They describe it as a temporary fix designed to give you time to rewrite any code that is broken, so it is not likely to be around for very long.

If you are interested in the technical details of why this was made, I recommend looking at this article by Itzak Ben Gann in SQL Server magazine, January 2009:

http://www.sqlmag.com/Articles/ArticleID/100697/100697.html