How aggregates are optimized

Aggregates are processed in two steps:

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.

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.