COUNT_BIG Function In SQL Server 2012

In this article you will learn about 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.

  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)  
  11.   
  12. selectfrom #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

  • 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.