Microsoft Issues Hotfix for SQL Server 2005 Ordered View and Inline Function Issue

My September 11, 2006 SQL Server 2005 Ordered View and Inline Function Problems post described a change of behavior between SQL Server 2000 and 2005 when displaying views created with a SELECT TOP 100 PERCENT ... ORDER BY Whatever query: SQL Server 2000 sorts the resultset and SQL Server 2005 [Express] doesn't.

The post received many comments, including several that objected to my claiming this behavior was an issue for SQL Server users because the behavior was by design. Unfortunately, comments posted before the change of the OakLeaf blog format were lost in the transition to the wider page.

Yesterday Microsoft issued a hotfix, FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2005, the result is still returned in random order, which is only available from Microsoft Support, describes the following 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.

The hotfix involves modifications to 11 files, including Sqlservr.exe and the workaround described in my post is simple, so Microsoft must have received many complaints about the problem.

Thanks to IDisposable (Marc Brooks) for the heads-up on the hotfix.

Technorati tags: Databases, SQL Server 2005, TOP (100) PERCENT, ORDER BY, SQL Server, SQLServer, MSSQL, SQL Server 2005 Express, SQL Server Express, SQL Express, da Vinci Toolset, Visual Data Tools, SQL Server Management Studio, Access 2007, Microsoft Access, Access Data Projects