Count(*) vs Count(1) - SQL Server

[ForumBot]

Count(*) vs Count(1) - SQL Server

[ForumBot]

There is no difference.

Reason:


[Books on-line](http://msdn.microsoft.com/en-us/library/ms175997.aspx) 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](https://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-col/2512#2512).

Edit, Dec 2011

`COUNT(*)` is mentioned specifically in [ANSI-92](http://msdn.microsoft.com/en-us/library/ms175997.aspx) (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 to each row of T
and eliminating null values. If one or more null values are
eliminated, then a completion condition is raised: warning-