Dirk Dot Net

Creator of useful things. Based in Chiang Mai, Northern Thailand.

100% View but no Order (at least not in SQL Server 2005)

In SQL Server 2000 you could add ORDER BY to the query defining a view that started with SELECT TOP (100) PERCENT... which is the default query when you create a view with SQL Server Enterprise Manager for SQL Server 2000.

The TOP statement would pretend to limit the resultset (nothing is actually limited when 100 percent are returned) and ORDER BY could be applied. However, things changed with SQL Server 2005. The query optimizer now removes both TOP and ORDER BY clauses if the query is for the TOP 100 PERCENT. From SQL Server Books Online:

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

This means that each (outer) query issued against the view must contain the ORDER BY statement to guarantee an ordered resultset. There is absolutely no warning in the query designer if you verify the syntax of your view:

image

Microsoft recently released a hotfix for legacy applications relying on the deprecated behaviour which remains unsupported.

Here's an in-depth view (no pun intended) of this issue and a workaround/hack (issuing TOP (Very Large Integer)).