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

COUNT(*) and COUNT(1): Performance Battle - SQL in Sixty Seconds #176 176-Countmatch-yt-800x450 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.

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

, , ,
Previous Post
COUNT(*) and Index – SQL in Sixty Seconds #175
Next Post
SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177

Related Posts

1 Comment. Leave new

  • Alexandre Neves
    July 27, 2021 10:54 pm

    Pinal

    I follow your work and have it as a reference, I am an enthusiast

    of performance and performance.

    Count(*) x Count(1) gives us a very similar millisecond difference result.

    I took the test on a base with over 127 Million records and below

    the evidence.

    I’ll continue with the count(*) as this is already in the blood, it goes automatically.

    Sucess

    DBA Alexandre Neves

    Reply

Leave a Reply

Menu