When used separately, max and min aggregates on leading index columns use special processing if there is no where clause in the query:
min aggregates retrieve the first value on the root page of the index, performing a single read to find the value.
max aggregates follow the last entry on the last page at each index level until they reach the leaf level.
However, when min and max are used together, this optimization is not available. The entire leaf level of an index is scanned to locate the first and last values.
min and max optimizations are not applied if:
The expression inside the max or min function is anything but a column. When numeric_col has a nonclustered index:
max(numeric_col*2) contains an operation on a column, so the query performs a leaf-level scan of the index.
max(numeric_col)*2 uses max optimization, because the multiplication is performed on the result of the function.
There is another aggregate in the query.
There is a group by clause.
