SQL Server – Single Scan for 3 Operations – COUNT(*) COUNT(1) SUM(1)

My receive videos on COUNT(*) COUNT(1) SUM(1) have been extremely popular. Today we will take the same conversation to the next level by discussing Single Scan for 3 Operations – COUNT(*) COUNT(1) SUM(1).

Before you continue this blog post, I suggest reading the following blog and watching the video.

• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175

Once you watch them, I have a question for you that I was asked during  Comprehensive Database Performance Health Check.

Question: What would happen if you use all the three COUNT(*) COUNT(1) SUM(1) together in a single SELECT statement? Will SQL Server do three index scans?

Well, the answer is pretty straightforward.

Answer: No, SQL Server Optimizer is very smart and knows that answer to all the three operations is the same and will just do a single scan. Here is the code, which you can use to run the test yourself. Make sure that you have enabled SQL Server Execution Plan when you run the query.

USE [AdventureWorks]
GO
-- Create sample table
SELECT *
INTO TestTable
FROM [AdventureWorks].[Person].[Address]
GO
-- Clustered index
CREATE CLUSTERED INDEX [CI] ON [dbo].[TestTable]
(
[AddressID] ASC
)
GO
-- Narrow non-clustered index
CREATE NONCLUSTERED INDEX [Narrow] ON [dbo].[TestTable]
(
[StateProvinceID] ASC
)
GO
-- Wide non-clustered index
CREATE NONCLUSTERED INDEX [Wide] ON [dbo].[TestTable]
(
[PostalCode] ASC
)
GO
-- Get Statistics On
SET STATISTICS IO ON
GO
SELECT COUNT(*) CountStar,
COUNT(1) CountOne,
SUM(1) SumOne
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

Now when you run the SELECT command with all three operations you will find the execution plan which is very similar to the following. Here, you can see that instead of three times, the SQL Server just does a one-time scan of the narrow table.

In summary, SQL Server knows what it is doing most of the time and SQL Server Optimizer makes the best decision in most cases.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171
• Best Value for Maximum Worker Threads – SQL in Sixty Seconds #170
• Copy Database – SQL in Sixty Seconds #169
• 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
• Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
• 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
• Get Memory Details – SQL in Sixty Seconds #165
• Get CPU Details – SQL in Sixty Seconds #164
• Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163

Reference: Pinal Dave (http://blog.SQLAuthority.com)