SQL 2005 Ordered Views

I experienced problems migrating SQL 2000 database to SQL 2005 because some Views don't follow the ANSI SQL rules using SELECT TOP n [PERCENT] and ORDER BY clause to order the query's internal rowset so as to return the correct resultset.

A Simple Workaround for the TOP (100) PERCENT Problem is substitute TOP (VeryLargeInteger) for TOP (100) PERCENT. The largest integer that the query parser will accept is MAX(int) or 2147483647 so you may use TOP(2147483647).
This limitation appears to be for backward compatibility, because SQL Server 2005 converts TOP (n) values to the bigint data type.

Source: http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html
Share on Google Plus

About Vittorio Pavesi

    Blogger Comment
    Facebook Comment

1 comments:

IDisposable said...

Microsoft released a hotfix that deals with this today (6/13/2007) here