COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

COUNT(*) and COUNT(1): Performance Battle – which one is better for performance? I got this question after watching my previous video COUNT(*) and Index – SQL in Sixty Seconds #175. Well, let us see a video about it here.

Before you start this video, I must say that theoretically, COUNT(1) may be better for performance over COUNT(*). However, I have yet not seen a difference between COUNT(*) and COUNT(1). I have seen quite a few articles suggesting that COUNT(1) can be faster and better. I am in no way saying they are wrong. I am saying that I am not able to recreate the scenario where there is any performance difference between COUNT(*) and COUNT(1).

If there is any reproducible demonstration where COUNT(1) performance better, let me know and I will be happy to build a demo around it and post it on the blog and youtube.

Here is the script used in this blog post about Performance Battle:

-- Create sample table
SELECT *
INTO TestTable
FROM [AdventureWorks].[Person].[Address]
GO
USE [AdventureWorks]
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(*)
FROM TestTable
GO
SELECT COUNT(1)
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

I hope you enjoyed this COUNT(*) and COUNT(1): Performance Battle.

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.

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