Select Count(*) vs Select Count(id) vs select count(1). Are these indeed equivalent?

Question

As a follow up to my earlier question:

Some of the answers and comments suggest that

select count(*) is mostly equivalent to select count(id) where id is the primary key.`

I have always favored select count(1); I even always use if exists (select 1 from table_name) ...

Now my question is:

1) What is the optimal way of issuing a select count query over a table?

2) If we add a where clause: where msg_type = X; if msg_type has a non_clustered index, would select count(msg_type) from table_name where msg_type = X be the preferred option for counting?

Side-bar:

From a very early age, I was taught that select * from... is BAD BAD BAD, I guess this has made me skeptical of select count(*) as well

Answer 1

count(*)  --counts all values including nulls

count(id)-- counts this column value by excluding nulls

count(1) is same as count(*)

If we add a where clause: where msg_type = X; if msg_type has a non_clustered index, would select count(msg_type) from table_name where msg_type = X be the preferred option for counting?

As i mentioned in my previous answer ,SQL server is a cost based optimizer and the plan choosen depends on many factors .sql tries to retrieve cheapest plan in minimum time possible..

now when you issue,count(msg_type),SQL may choose this index if this is cheaper or scan another one as long as it gives right results(no nulls in output)..

I always tend to use count(*) ,unless i want to exclude nulls,for the reasons quoted here

Answer 2

Well, those count queries are not identical and will do different things.

select count(1)
select count(*)

Are identical, and will count every record !

select count(col_name)

Will count only NOT NULL values on col_name !

So, unless col_name is the PK as you said, those query will do different things.

As for you second question, it depends, we can't provide you a generic answer that will always be true. You will have to look at the explain plan or just check for your self, although I believe that adding this WHERE clause while having this index will be better.