Difference between select count(*), count(0), count(100), count(Id) in SQL Server 2008?

Question

What is the difference between following SQL Server 2008 statements?

SELECT COUNT(*) FROM dbo.Regular_Report

SELECT COUNT(0) FROM dbo.Regular_Report

SELECT COUNT(1) FROM dbo.Regular_Report

SELECT COUNT(100) FROM dbo.Regular_Report

SELECT COUNT(ID) FROM dbo.Regular_Report

Answer 1

The first four are all identical - they count the total number of rows.

The last one counts the number of rows where ID is not null.

Answer 2

count(id) will count the non-null values in the id field.

All the other will count the number of records.

(The ones with literal numbers instead of * will count all the records where that value is not null. Probably the query planner will recognise that the literal value will never be null for any record, and do the same as count(*) instead of actually counting the non-null values.)