SQL Server Management Studio TOP (100) PERCENT with ORDER BY Misleads Users by Roger Jennings

It appears to be reasonably well known by now that an SQL Server 2005 view or TVF with a SELECT TOP (100) PERCENT ... ORDER BY query differs from SQL Server 2000 behavior by not returning an ordered resultset. Users have discovered that a large number (such as MAX(int), 2147483647) as the TOP argument causes the SQL Server 2005 query engine to emulate SQL Server 2000 behavior with TOP 100 PERCENT.

The Microsoft party line on this issue appears to be:

1. SQL Server 2000's behavior occurred "by chance"

2. There is no guarantee that the ORDER BY clause in a view or TVF will order the resultset, even if it appears to do so.

3. One should only expect the ORDER BY clause to order resultsets in the outermost SELECT query (per ANSI SQL).

If that is the party line, the addition of TOP (100) PERCENT as the default TOP operator by SSMS misleads users. TOP (100) PERCENT doesn't constrain the resultset, so its only purpose can be to sort the resultset. If the query processor team is convinced of their position they should embargo use of TOP (100) PERCENT (and TOP 100 PERCENT from the Access Upsizing Wizard), not insert it as a default when a user adds an ORDER BY clause. Further, the Sort Type list and Sort Order text box should be removed from the da Vinci toolset's Criteria pane when designing views.

It seems to me that the party line is arbitrary (and capricious, for that matter). TOP isn't ANSI, so compliance isn't an issue. There is good reason for users to believe that the resultset of any SELECT TOP query should be sorted by the ORDER BY clause, especially when they see Sort Type and Sort Order choices, and TOP (100) PERCENT added by SSMS.

Users also see TOP 100 PERCENT added by the Access Upsizing Wizard, which doesn't work with SQL Server 2005. (Apparently someone forgot to tell the Access team about this early enough to fix it for Access 2007 Beta2TR.)

Further, the SSMS da Vinci Results pane grid for views displays SELECT TOP (100) ... ORDER BY resultsets in sorted order, because in this case the SELECT is the outermost query. This display will mislead unforewarned users who don't run the view and inspect it closely.

The appropriate solution for SQL Server 2005 [Express] SP2 is to require SQL Server 2005's query processor to respect SELECT TOP (100) PERCENT ... ORDER BY views and TVFs by emulating SQL Server 2000 behavior, and change the party line by guaranteeing that SELECT TOP ... ORDER BY views and TVFs return a sorted resultset.

See my http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html blog post for illustrated examples of this bug.