Query tuning and optimization is my favourite thing when I am working on a Comprehensive Database Performance Health Check. The other day while working with a client, I realized that they have a query where they are using an aggregate function like MIN and MAX. The question I received from my client was – Do MAX(col) Scan Table? Let us learn about that today.
One of the common misconceptions is that as MAX and MIN like operators which are only retrieving only single row use some kind of smart logic or algorithm to get the necessary results. Actually, it is not true in most cases. All the aggregate functions use the similar SQL Server Engine and algorithm which is needed to read/retrieve the same data.
Here is the SQL in the Sixty Seconds video that explains the entire scenario with an example.
Here is the script which I had used in this SQL in the Sixty Seconds video. The query is based on the sample database WideWorldImporters. Here is the blog post which talks about how you can install sample database in your SQL Server.
USE WideWorldImporters GO SELECT MAX([InvoiceID]) MaxValue FROM [Sales].[Invoices] GO SELECT MAX([TotalChillerItems]) MaxValue FROM [Sales].[Invoices] GO
As explained in the video above, it is quite possible that sometimes an aggregate function will do a scan and sometimes they will do seek if there is an efficient index available for the query. If any query is returning only one row it does not mean it does not have to scan the table, it is quite possible it is, in fact, doing much more work compared to the queries which are retrieving few rows.
I have been recently creating many SQL in the Sixty Seconds video. If you like the video, please go ahead subscribe YouTube channel. If you want me to build a videos on any topic, leave a comment and I will be happy to build short videos on that topic.
Reference: Pinal Dave (https://blog.sqlauthority.com)
