Do MAX Function Scan Table? – Interview Question of the Week #289

Question: Does MAX scan the whole table? It can. A useful ordered index may let SQL Server find the endpoint with very little work; without that access path, the plan may read many or all qualifying rows.

I don’t like an interview answer that stops at “it depends.” The practical way to answer is to compare two columns on the same table, inspect the actual plan and measure logical reads. That is what the original WideWorldImporters demonstration did.

Run the original comparison

Use the WideWorldImporters sample, and enable the actual execution plan in SSMS:

USE WideWorldImporters;
GO
SET STATISTICS IO ON;
SELECT MAX(InvoiceID) AS MaxValue FROM Sales.Invoices;
SELECT MAX(TotalChillerItems) AS MaxValue FROM Sales.Invoices;
SET STATISTICS IO OFF;

Those are measurements from the original sample run, not promises for every server or copy of the database. Its InvoiceID index supplied an efficient ordered access path. Finding the maximum of the other column required inspecting the data containing that column.

Read the operator’s actual work

An operator named Index Scan does not necessarily read the whole index. An ordered backward scan under Top can stop after reaching the needed endpoint. Conversely, an Index Seek can still read many rows. Look at the actual number of rows read, scan direction, predicates and logical reads before describing a plan as cheap or expensive.

The original plan picture’s added “Seek” label was misleading. On the SQL Server 2025 lab, both operators below are Clustered Index Scan, but their actual work is very different.

MAX(InvoiceID) returned 70510. Its ordered backward scan below Top read one row; STATISTICS IO reported three logical reads.

MAX(TotalChillerItems) returned 3. This unordered parallel scan read 70,510 rows across its executions, with 11,994 logical reads. Those are measurements from this sample and run, not guarantees for every copy of the database.

A predicate or grouped MAX changes the access requirement. An index on another column is not automatically useful. MAX ignores NULL values and returns NULL when there is no value to aggregate; a TOP-based rewrite must preserve those semantics.

A scan isn’t automatically a problem. For a query that needs much of the data, it may be sensible. If this aggregation is expensive and frequent, evaluate an appropriate index against its storage, insert/update cost and the rest of the workload, then retest.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.