Difference between count(*), count_big(*) and count(<some columnname>

 Posted by Ddd on 2/4/2011 | Category: Sql Server Interview questions | Views: 5485 | Points: 40

count(*): gives the total number of rows in a table

count_big(*): also gives the total number of rows in a table

difference: count(*): returns int datatype value

count_big(*):returns bigint datatype value

count(<some columnname>: returns the total number of rows of a column

where the value of the column is not null

Consider this table emp

eno ename salary
100 dd 6000
200 ss 6500
400 ddd NULL

now run the query:

select count(*), count_big(*), count(salary) from emp

output will be

3 : below the count(*) column
3: below the count_big(*) column
2: below the count(salary)

(no column name will be displayed
for any of the columns since no alias has been used)

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response