COUNT_BIG Function In SQL Server 2012

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 } | * )

ALL: Count function applies the aggregate function to all values. This is the default.
DISTINCT: This specifies COUNT_BIG function returns the number of unique non-null values.
Expression: It is an expression of any type. This does not work with aggregate functions and subqueries.
*: This specifies that all the rows should be counted to return total number of rows in query. COUNT_BIG (*) does not take an expression as argument. Also includes null value of column.

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.

Create table #emp (
Id int,
name varchar(50)
) --Inserting some dummy data
Insert into #emp values(1,'Jignesh')
Insert into #emp values(2,'Tejas')
Insert into #emp values(1,'Jignesh')
Insert into #emp values(2,'Tejas')
Insert into #emp values(1,'Jignesh 1')
Insert into #emp values(null,null)
select* from #emp

Query: SQL Query with ALL attribute.

SELECT COUNT_BIG(ALL Id) AS EmpCount FROM #emp

Output

Query output

Query: SQL Query with DISTINCT attribute.

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:

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

COUNT_BIG (*) returns the number of items in a group and it also includes NULL values and duplicates.
COUNT_BIG (ALL expression) evaluates expression for each row in a group and includes duplicate data and non-null values count.
COUNT_BIG (DISTINCT expression) evaluates expression for each row in a group and returns the number of unique non-null values.

7 Comments

Join the conversation! Your thoughts help the community grow.

Santhakumar Munuswamy11y ago
Good one
Banketeshvar Narayan11y ago
Nice share
Yashwant Vishwakarma11y ago
good explanation !!!
Raja T11y ago
Nice One,thanks for sharing
Sibeesh Venu11y ago
Nice Share
Harshad Pansuriya11y ago
Nice one
Humayun Kabir Mamun11y ago
Nice...