Count(*) vs Count(Id) in sql server 2005

Question

I use Count function to get the total number or rows from a table...

• Is there any difference between the two following statements?

  Select Count(*) from Table

and

    select Count(Id) from Table

Is there a difference between execution times...

Answer 1

count(id) needs to null-check the column (which may be optimized away for a primary key or otherwise not-null column), so count(*) or count(1) should be prefered (unless you really want to know the number of rows with a non-null value for id).

Answer 2

Thilo nailed the difference precisely... COUNT( column_name ) can return fewer than COUNT( * ) if column_name values can be NULL.

However if I can take a slightly different angle at answering your question, since you seem to be focusing on performance.

First, note that issuing SELECT COUNT(*) FROM table will potentially block writers, and it will also be blocked by other readers/writers unless you have altered the isolation level (knee-jerk tends to be NOLOCK but I'm seeing a promising number of people finally starting to believe in snapshot). If you need an absolutely transactionally consistent and accurate row count (even if it is only valid for the number of milliseconds it takes to return the number to you), then SELECT COUNT( * ) is your only choice.

On the other hand, if you are trying to get a 99.9% accurate ballpark, you are much better off with a query like this:

SELECT row_count = SUM(row_count)
  FROM sys.dm_db_partition_stats
  WHERE [object_id] = OBJECT_ID('dbo.Table')
  AND index_id IN (0,1);

(The SUM is there to account for partitioned tables - if you are not using table partitioning, you can leave it out.)

This DMV maintains accurate row counts for tables with the exception of rows that are currently participating in transactions - and those very transactions are the ones that will make your SELECT COUNT query wait. But otherwise this will lead to a much quicker answer than the query you propose, and no less accurate than using NOLOCK.

Answer 3

Already answered but pretty good discussion about this here:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/31795984-ea62-4b2c-8c78-6e986f2bcea0