Thursday, June 14, 2007

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: , , , , , , , , , , , , , , ,

3 comments:

IDisposable said...

Minor niggle... it's Marc, not Mark :)

Manjoor said...

Sorry, But the problem is as it is. I am using SQL server 2005 Entrprise edition. I have installed SP2 and the latest hotfix patch released on 18 Jun 2007. But I am still gettting records in random order after quering view.

Anonymous said...

We converted our website server to SQL 2005 last year and I had to argue with so many people about this issue just to get a few people to acknowlege it shouldn't be "by design". The hotfix requires ME to update 11 files or does the hotfix update the files itself?
Nice to know they finally released something to admit it's an issue but I already converted all my queries to request the info at the client rather then rely on the views.