SQL Server 2005 & Ordered Views Gotcha

I recently ran into an issue where we were using an "undocumented feature" in SQL Server 2000, that allowed us to sort a view and then when selecting all items from the view the items returned in the select statement would follow the same order as the view. This does not work with SQL Server 2005, because the undocumented feature has been corrected. Lets say that we have the following table and data.

tblSortedUsers

Id	UserName	Sortorder
1	Bob	4
2	Tim	3
3	Sally	2
4	John	1

Now we create the view vwSortedUsers as follows:

CREATE VIEW vwSortedUsers AS
SELECT TOP 100 PERCENT
    Id, UserName
FROM tblSortedUsers
ORDER BY SortOrder

Now when execute the following query:

SELECT * FROM vwSortedUsers

I will get the following results:

SQL Server 2000

Id	UserName
4	John
3	Sally
2	Tim
1	Bob

SQL Server 2005

Id	UserName
1	Bob
2	Tim
3	Sally
4	John

The reasoning for this this best explained by the excerpt below from Microsoft KB Article 926292:

SYMPTOMS

You have a view in a database in SQL Server 2005. In the definition of the view, the SELECT statement meets the following requirements:

• The SELECT statement uses the TOP (100) PERCENT expression.

• The SELECT statement uses the ORDER BY clause.

When you query through the view, the result is returned in random order.

However, this behavior is different in Microsoft SQL Server 2000. In SQL Server 2000, the result is returned in the order that is specified in the ORDER BY clause.

This discussion talks about this being an undocumented feature that was corrected in SQL Server 2005 and also references the above MS KB Article. While the KB article mentions a HotFix that can be applied to SQL Server 2005, the best (and recommended) solution is to add the Order By clause to your query and not your view. Therefore, the query should be:

SELECT * FROM vwSortedUsers ORDER BY SortOrder

So if you are in the process of upgrading your database from SQL Server 2000 to SQL 2005, please aware of this issue.