INFO: Improving Performance in Views with Aggregate Functions (89384)

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 "SELECT * FROM MyView" 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 ("SUM(col3)") 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