How aggregates are optimized

Aggregates are processed in two steps:

First, appropriate indexes are used to retrieve the appropriate rows, or a table scan is performed. For vector (grouped) aggregates, the results are placed in a worktable. For scalar aggregates, results are computed in a variable in memory.
Second, the worktable is scanned to return the results for vector aggregates, or the results are returned from the internal variable.

Vector aggregates can use a covering composite index on the aggregated column and the grouping column, if any, rather than performing table scans. For example, if the titles table has a nonclustered index on type, price, the following query retrieves its results by scanning the leaf level of the nonclustered index:

select type, avg(price)
    from titles
    group by type

Scalar aggregates can also use covering indexes to reduce I/O. For example, the following query can use the index on type, price:

select min(price)
    from titles

Table 5-1 shows some of the access methods that the optimizer can choose for queries with aggregates when there is no where, having or group by clause in the query.

**Table 5-1: Special access methods for aggregates**
Aggregate	Index description	Access method
min	Scalar aggregate is leading column	Use first the value on the root page of the index.
max	Clustered index on an allpages-locked table	Follow the last pointer on root page and intermediate pages to data page, and return the last value.
max	Clustered index on a data-only-locked table Any nonclustered index	Follow last pointer on root page and intermediate pages to leaf page, and return the last value.
*count()**	Nonclustered index or clustered index on a data-only-locked table	Count all rows in the leaf level of the index with the smallest number of pages.
count(col_name)	Covering nonclustered index, or covering clustered index on data-only-locked table	Count all non-null values in the leaf level of the smallest index containing the column name.