Very common interview question about COUNT aggregate function.

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)

)

GO

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

GO

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

GO

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
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com