is count(indexed column) faster than count(*)? [duplicate]

Question

Possible Duplicate:
Performance of COUNT SQL function

Hi all, I've very large tables and I need to know number of records in each , My question is does it reduce the run time if I run :

select count(indexed column like my PK) from tbTest

instead of

select count(*) from tbTest

Answer 1

see Performance of COUNT SQL function

The important thing to note is they are not equivalent

Answer 2

Since the question is whether or not there is a performance difference, it would depend on the index. When you do COUNT(*), it will use the PK column(s) to determine the number of rows. If you do not have any indexes besides a clustered index on the PK column(s), it will scan the leaf nodes on the clustered index. That's probably a lot of pages. If you have a non clustered index that is skinnier than the clustered index, it will choose that instead, resulting in less reads.

So, if the column you select is contained in the smallest possible non-clustered index on the table, the SQL query optimizer will choose that for both count() (if you have a clustered ix that is the PK) and count(indexed_column). If you choose a count(indexed_col) that is only contained in a wide index, then the count() will be faster if your PK is a clustered index. The reason this works is that there is a pointer to the clustered index in all non-clustered indexes and SQL Server can figure out the number of rows based on that non-clustered index.

So, as usual in SQL Server, it depends. Do a showplan and compare the queries to each other.