1

SQL Server - Single Scan for 3 Operations - COUNT(*) COUNT(1) SUM(1) - SQL Autho...

 3 years ago
source link: https://blog.sqlauthority.com/2021/06/30/sql-server-single-scan-for-3-operations-count-count1-sum1/?utm_campaign=sql-server-single-scan-for-3-operations-count-count1-sum1
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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).

SQL Server - Single Scan for 3 Operations - COUNT(*) COUNT(1) SUM(1) singlescancover-800x330

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

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.

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

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.

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


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK