Introduction

COUNT_BIG returns the number of items within a group. It works same as COUNT function. The only difference between these two functions is the return type. COUNT_BIG returns bigint data type whereas COUNT returns int data type value.

Syntax

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

Example:

In the following example, I have created one temporary table and inserted some dummy data in the table. I have also added row with null data.

  1. Create table #emp (
  2. Id int,
  3. name varchar(50)
  4. ) --Inserting some dummy data
  5. Insert into #emp values(1,'Jignesh')
  6. Insert into #emp values(2,'Tejas')
  7. Insert into #emp values(1,'Jignesh')
  8. Insert into #emp values(2,'Tejas')
  9. Insert into #emp values(1,'Jignesh 1')
  10. Insert into #emp values(null,null)
  12. select* from #emp
See result

Query: SQL Query with ALL attribute.
  1. SELECT COUNT_BIG(ALL Id) AS EmpCount FROM #emp
Output

Query output

Query: SQL Query with DISTINCT attribute.
  1. SELECT COUNT_BIG(DISTINCT Id) AS EmpCount FROM #emp
Output

Output

In above two queries, SQL server engine ignore null value row while counting row.

Query:
  1. SELECT COUNT_BIG(*) AS EmpCount FROM #emp
Output:

Result

COUNT (*) Vs COUNT_BIG (*)

Count (*) Count_Big (*)
It returns int data type value It returns bigint data type value
We cannot create cluster index on view when view has COUNT (*) in it Index could be created if it has COUNT_BIG (*)

Summary

COUNT_BIG works similar to COUNT function. It is used to count particular item in a group.

Point to remember