Question: Do MAX Function Scan Table to retrieve the data?
Answer: Yes, if there are no indexes to help retrieve data.
I personally hate answers which are ambiguous and I also do not like to say it depends as well. Let me say in simple possible words what I have seen in the real world.
When you retrieve data from a column and if there is no index on the column (or if SQL Server thinks scan is faster) it will do the table scan. If the SQL Server engine finds the index which can help you query to retrieve the data faster, it will use the index and get you results for the MAX operator.
Here is a simple demonstration of the sample database WideWorldImporters. Here is the instruction to download them.
Run the following command by enabling execution plan: to see the MAX function scan.
SET STATISTICS IO ON SELECT MAX([InvoiceID]) MaxValue FROM [Sales].[Invoices] GO SELECT MAX([TotalChillerItems]) MaxValue FROM [Sales].[Invoices] GO
When I ran the script above here is what I got as a statistics IO.
Table ‘Invoices’. Scan count 1, logical reads 3
Table ‘Invoices’. Scan count 17, logical reads 11994
The first query only reads 3 pages but the second query which is on the same table but different column reads 11994 pages, which are pretty much all the pages of the table.
This image lists the execution plan for the query.
So essentially, do not think MAX will just read one or two rows to get you data. It is quite possible that it will scan the entire table to get you the necessary data. Use the aggregated function carefully, or they can turn out to be extremely expensive for your system.
Please note that I am not saying scan is bad and seek is good. What I am saying is that when you use aggregated function sometimes the entire table scan happens and that is not recommended if your table is large. In that case, you can take help of the index to improve your queries performance.
Reference: Pinal Dave (https://blog.sqlauthority.com)
