SQL SERVER – SUM(1) vs COUNT(*) – Performance Observation

I have received lots of comments and emails about my recent two SQL in Sixty Seconds Videos  COUNT(*) and Index – SQL in Sixty Seconds #175 and COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176. One question that kept on coming up was SUM(1) vs COUNT(*) – Performance Observation. Well, let us see that today.

Lots of people find it surprising that COUNT(*) and COUNT(1) gave exactly the same performance. Many even asked if which one is better SUM(1) or COUNT(*). The answer is very simple and straightforward. Both are equal.

I have run the demonstration which I have earlier shared here.

During comparison of SELECT I ran the following query:

SET STATISTICS IO ON
GO
SELECT COUNT(*)
FROM TestTable
GO
SELECT COUNT(1)
FROM TestTable
GO
SELECT SUM(1)
FROM TestTable
GO

Now when you compare the performance of the three queries, it is actually the same and they all use the same Narrow Index.

Additionally, when you compare the statistics IO you will see exactly the same information.

Essentially SUM(1) vs COUNT(*)  are exactly the same regarding Performance Observation.

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.

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