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

My Performance Observation found similar counting plans for a nonempty table. SUM(1) and COUNT(*) still differ on empty input.

CREATE TABLE #TestTable (ID int NOT NULL, Padding char(100) NULL);
CREATE INDEX IX_TestTable_ID ON #TestTable(ID);
INSERT #TestTable(ID) VALUES (1),(2),(3);
SET STATISTICS IO ON;
SELECT COUNT(*) AS CountStar FROM #TestTable;
SELECT COUNT(1) AS CountOne FROM #TestTable;
SELECT SUM(1) AS SumOne FROM #TestTable;
SET STATISTICS IO OFF;
DELETE #TestTable;
SELECT COUNT(*) AS EmptyCount,SUM(1) AS EmptySum FROM #TestTable;
DROP TABLE #TestTable;

My earlier example used the same narrow index and logical reads for three shapes. That was a specific test. The temporary-table companion has its own size and plan. It does not reproduce the earlier timings.

On empty input, COUNT(*) returns zero and SUM(1) returns NULL. COUNT(1) counts every row. Datatype and overflow choices also matter for large inputs. Use COUNT_BIG when the count can exceed int.

I choose the expression that states the intended result. Then I compare actual plans and reads for representative data. Preserve correct behavior before optimizing performance.

Related reading

• COUNT(*) and Index – SQL in Sixty Seconds #175
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• SQL in Sixty Seconds
• 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

A matching test plan is not universal expression equivalence, it is an observation under specific data and conditions.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.