BUG: TOP 100 PERCENT in View Runs Slower On Service Pack 3 (297469)

MORE INFORMATION

In general, when you query a view you must think of the view as a virtual table whose results have been materialized during the query. In cases where the view definition does not include the TOP clause, some of the restrictions used in the query against the view can be "pushed" down into the view computation itself so that fewer rows are required to be materialized. However, if the view contains the TOP clause, this optimization may cause incorrect results. The only time where this optimization would be valid is in the case of TOP 100 PERCENT, where you will get the same results regardless of whether the filtering is during the view materialization or afterwards.

SQL Server does not allow you to create a view with an ORDER BY clause unless there is also a TOP operation in the view. Some customers have attempted to create a view that only selects the top 100 percent of the rows ordered by some criteria, hoping that a select from the view will always return results ordered in the fashion indicated in the view.

As with a SELECT from any other table, the order in which results are returned to the client is undefined unless the query specifies an ORDER BY clause as part of the query. In the case of a view whose definition includes a TOP or ORDER BY clause, the ORDER BY clause in the view is allowed solely for the purpose of defining an ordering for the rows prior to the TOP operation being performed. For example, if you want to create a view that selects the 10 most recently ordered items from an Orders table, the view is expressed as:

USE northwind
GO
CREATE VIEW dbo.vwLastTenOrders AS SELECT TOP 10 * FROM Orders ORDER BY OrderDate DESC
				

SQL Server does not guarantee that a query from a view that contains a TOP or ORDER BY clause will return the results ordered in the same fashion as was requested in the view. For simple queries against the view the results may appear to be ordered, but they are not guaranteed to be ordered. In some cases the SELECT performs joins or other relational operations against the view, and you may find that the results are not always ordered. If an application expects ordered results when querying a view, you must include the ORDER BY clause in that query. For example:

SELECT * FROM northwind.dbo.vwLastTenOrders WHERE ShippedDate IS NOT NULL ORDER BY OrderDate
				

For the preceding example, the query plan is similar to the text that follows.

NOTE: To ensure that the query returns correct results, the filter for the IS NOT NULL clause must be performed after the view has materialized.

  |--Sort(ORDER BY:([Orders].[OrderDate] ASC))
       |--Filter(WHERE:([Orders].[ShippedDate]<>NULL))
            |--Top(10)
                 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Orders]))
                      |--Index Scan(OBJECT:([Northwind].[dbo].[Orders].[OrderDate]), ORDERED)