Very common interview question about COUNT aggregate function.

Tuesday, November 24, 2009 |

I have observed that many interviewers asks question to SQL Server Developer about COUNT aggregate function. The question is something like

There is one table which has three fields.

1.)    ID which is integer primary key so it won’t accept null value
2.)    FirstName which varchar and can accept null value
3.)    LastName which varchar and can accept null value

If I execute COUNT(*) in SELECT query and in other SELECT query, I execute COUNT(ID) and in third SELECT statement I execute COUNT(FirstName), what would be the results? Whether it is same or not?

Most of the SQL Server developer says, it would be same with very high confidence which is not true in all case. Let us see one small example.

create table countTest
      id int primary key,
      firstname varchar(10),
      lastname varchar(10)

insert into countTest
select 1,'ritesh','shah' union all
select 2,null,'jain' union all
select 3,'rajan',null union all
select 4,'alka',null

select count(*) as CountStar from countTest --out put would be 4
select count(id) as countID from countTest --out put would be 4
select count(firstname) as countFirstName from countTest --out put would be 3
select count(lastname) as countLastName from countTest --out put would be 2

So after seeing last two SELECT statements, your confidence level will become zero because COUNT aggregate function will not consider NULL value. Not only COUNT but other aggregate functions like SUM, AVG etc. will follow the same rule.

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of