Simply due to sanity reasons. I heard the rumor way back when in the dark years of my career when I had to work with Oracle that it was possible that count(*) extended to columns' sniffing, which was a pure waste in most cases, and therefore, it was best to avoid it. I believe that in those days it was possible to make the select count(*) from the_table statement break miserably should the calling user be denied of select on one of the columns in the table. In a mean time, count(1) guaranteed to return accurate count regardless of the columns and their respective nullability. Actually, Oracle developers of the last millennium frequently preferred to use count('X'), which offered superior performance when compared to

select count(anything_star_included_but_not_the_nullable_column_name) from the_table;

While this is true that in newer versions of Oracle count('X') is no longer special, and in SQL Server it does not make any difference whether count(*) or count(1) is used, many people like me still opt to avoid count(*) at all costs, just for sanity check if anything.

I understand that in SQL Server if the user executing the statement has explicit deny on any of the columns then the statement will fail regardless of whether count(1) or count(*) was used, but this is a rare scenario, which will force such user to use something like

select count(not_nullable_column) from the_table;

In this regard, there is no difference between count(1) and count(*), but I still like count(1) 🙂

For example, if I have a user named TestUser in AdventureWorks database and the user has explicit select permission on HumanResources.Department table and explicit deny on ModifiedDate column like this:

grant select on HumanResources.Department to TestUser;

go

deny select on HumanResources.Department (ModifiedDate) to TestUser;

go

-- then

exec ('select count(DepartmentID) RecordCount

from HumanResources.Department') as user = 'TestUser';

-- runs just fine, returning 16 as RecordCount, but either

exec ('select count(1) RecordCount

from HumanResources.Department') as user = 'TestUser';

-- or

exec ('select count(*) RecordCount

from HumanResources.Department') as user = 'TestUser';

fails with:

Msg 230, Level 14, State 1, Line 1

The SELECT permission was denied on the column 'ModifiedDate' of the object 'Department', database 'AdventureWorks', schema 'HumanResources'.

Oleg