Microsoft KB Archive/297469

= BUG: TOP 100 PERCENT in View Runs Slower On Service Pack 3 =

Article ID: 297469

Article Last Modified on 9/4/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q297469



BUG #: 101572 (SQLBUG_70)



SYMPTOMS
A query from a view whose definition includes &quot;TOP 100 PERCENT&quot;, may run slower on a computer that is running Microsoft SQL Server 7.0 Service Pack 3 (SP3).



CAUSE
All of the filter or join conditions that are used in the SELECT statement from the view are applied after the view has materialized. Prior to SQL Server 7.0 SP3, some of the filter or join conditions may have been pushed down into the view definition so that the view was only partially materialized, which resulted in faster performance.



WORKAROUND
If the view definition contains a TOP 100 PERCENT clause but does not specify an ORDER BY clause, remove the TOP 100 PERCENT clause as it is redundant. If the view definition currently contains an ORDER BY clause, remove the ORDER BY clause from the view definition and add it to the query that is selecting from the view.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 Service Pack 3.



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 &quot;pushed&quot; 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)

Keywords: kbbug kbpending KB297469

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.