Select Count (*) vs Select Count (id) and select count (1). Are they really equivalent?

Question

As a continuation of my previous question :

Some answers and comments suggest that

select count(*) is basically equivalent to select count(id) , where id is the primary key.

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

Now my question is:

1) What is the best way to send select count query across 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 preferred option for counting?

Side bar:

From a very young age I was taught that select * from... BAD BAD BAD , I think it was skeptical about the choice of count (*), and also

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, will select count (msg_type) from table_name, where msg_type = X is the preferred option for counting?

As I mentioned in the previous answer, the SQL server is a cost optimizer and the chosen plan depends on many factors .sql is trying to get the cheapest plan in the minimum time possible ..

now that you are count(msg_type) , SQL can select this index if it is cheaper or scan another if it gives the results right (no zeros in the output) ..

I always use count(*) if I don't want to exclude nulls, for reasons.

Answer 2

Well, these counters are not identical and will do different things.

 select count(1) select count(*) 

Identical and will count every entry!

 select count(col_name) 

Will only count NOT NULL values ​​on col_name !

So, if col_name not PK , as you said, this query will do different things.

As a second question for you, it depends, we cannot provide you with a general answer that will always be true. You will have to look at the plan of explanation or just check your “I”, although I believe that adding this WHERE would be better.