Microsoft KB Archive/89384

= INFO: Improving Performance in Views with Aggregate Functions =

Article ID: 89384

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q89384



SUMMARY
To increase performance when selecting from a view, do not evaluate aggregate functions in the view, if possible.



MORE INFORMATION
To illustrate this idea, assume the following table and view definitions: CREATE TABLE MyTable (  col1 INT,   col2 CHAR(5),   col3 FLOAT) go

CREATE VIEW MyView AS  SELECT col1, col2, Total = SUM(col3) FROM MyTable WHERE col1 > 55 go If the statement &quot;SELECT * FROM MyView&quot; is executed, SQL Server will need to sum all the values in the table for col3 that match the WHERE condition of the view. However, if the aggregate column in the view (&quot;SUM(col3)&quot;) is not included in a SELECT statement, the values in col3 will not be summed. Therefore, if a large number of rows meet the condition(s) of the WHERE clause in the view, a considerable performance gain can be realized by not selecting that column unless it is needed.

The following queries are examples where the summing of col3 from the view will not be performed: - SELECT col1 FROM MyView - SELECT col2 FROM MyView - SELECT col1, col2 FROM MyView

Keywords: kbinfo kbsqlprog kbprogramming KB89384

-

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

© Microsoft Corporation. All rights reserved.