Count(*) vs Count(1) - SQL Server
performance, sql, sql-server
Solution
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 optimizer recognizes it for what it 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
Edit, Aug 2011
Similar question on DBA.SE.
Edit, Dec 2011
`COUNT(*)` is mentioned specifically in ANSI-92 (look for "`Scalar expressions 125`")
Case:
a) If COUNT(*) is specified, then the result is the cardinality of T.
That is, the ANSI standard recognizes it as bleeding obvious what you mean. `COUNT(1)` has been optimized out by RDBMS vendors because of this superstition. Otherwise it would be evaluated as per ANSI
b) Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning-
Problem
Just wondering if any of you people use `Count(1)` over `Count(*)` and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone past? The specific database is `SQL Server 2005`.