COUNT(*) vs COUNT(column)

Exploring the differences between COUNT(*) vs COUNT(column) in SQL Server and which one to use in day to day use

Which one to use

Unless you need a very specific answer, you are best off using the *. The query optimiser should choose to use the most suitable index.

There are a few differences in the syntax, so we can have a quick look at those first;

COUNT(*) returns the number of rows in the table
COUNT(COLUMN) returns the number of non-NULL values in the column
COUNT (DISTINCT COLUMN) returns the number of distinct non-NULL values in the column
COUNT (DISTINCT *) is not allowed, you would need to add a GROUP BY clause for this.

Test SQL

DECLARE @T TABLE(ID INT IDENTITY(1,1),Val VARCHAR(10))DECLARE @I INT=1WHILE @I<=100 BEGININSERT INTO @T SELECT (CASE WHEN @I % 3 = 0 THEN NULL ELSE @I END)INSERT INTO @T SELECT (CASE WHEN @I % 9 = 0 THEN NULL ELSE @I END)SET @I=@I+1ENDSELECT COUNT(*),COUNT(Val),COUNT(DISTINCT Val)FROM @T

Wrapping up

The results will return 200, 156 and 89 as we have NULLED out anything divisible by 3 and anything divisible by 9 up to 100.

There is a practical use for counting the column, but when you just need a row count use the *, particularly over non-indexed columns.