3

What is the difference between COUNT(*) and COUNT(table.ColumnName)?

I always thought that it is faster to put a column name as a parameter for COUNT, but now when I think of it I really don't know because * means that the function counts rows, so probably there isn't any difference? Or maybe giving a specific column name even slows the process?

Jeroen
  • 60,696
  • 40
  • 206
  • 339
nelaed
  • 187
  • 1
  • 2
  • 17

2 Answers2

8

The difference between these two is not (primarily) performance. They count different things:

COUNT(*) counts the rows in your table.

COUNT(column) counts the entries in a column - ignoring null values.

Of course there will be performance differences between these two, but that is to be expected if they are doing different things. Especially when the column allows null-values, the query will take longer than on a column that does not (or COUNT(*)).

germi
  • 4,628
  • 1
  • 21
  • 38
4

COUNT(*) returns a count of all records

COUNT(table.ColumnName) returns a count of all non-null values.

See Microsoft documentation:

http://technet.microsoft.com/en-us/library/ms175997.aspx

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that COUNT returns the number of unique nonnull values.

Curtis
  • 101,612
  • 66
  • 270
  • 352