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.

SQL SERVER - SUM(1) vs COUNT(*) - Performance Observation PerformanceObservation-800x391

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.

SQL SERVER - SUM(1) vs COUNT(*) - Performance Observation sum1

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

SQL SERVER - SUM(1) vs COUNT(*) - Performance Observation sum2

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.

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

, ,
Previous Post
SQL SERVER – COUNT(*) Collection of Articles
Next Post
SQL Server – Single Scan for 3 Operations – COUNT(*) COUNT(1) SUM(1)

Related Posts

Leave a Reply

Menu