ProAnswers.org

difference Count(*) vs Count(1) ?

if anyone use Count(1) over Count(*) and is there a noticeable difference for SQL Server 2005 in performance?

1 Like

There is no difference.

Reason:

Books on line says “COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )”

“1” is a non-null expression: so it’s the same as COUNT(*). The optimiser recognises it for what is is: trivial.

The same as EXISTS (SELECT * … or EXISTS (SELECT 1 …

Example:

SELECT COUNT(1) FROM dbo.tab800krows
SELECT COUNT(1),FKID FROM dbo.tab800krows GROUP BY FKID

SELECT COUNT() FROM dbo.tab800krows
SELECT COUNT(
),FKID FROM dbo.tab800krows GROUP BY FKID

Same IO, same plan, the works