THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

This function returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Topic link icon Transact-SQL Syntax Conventions


-- Syntax for SQL Server and Azure SQL Database  

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )  
   [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

-- Aggregation Function Syntax  
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )  

-- Analytic Function Syntax  
COUNT_BIG ( { expression | * } ) OVER ( [ <partition_by_clause> ] )  


Applies the aggregate function to all values. ALL serves as the default.

Specifies that COUNT_BIG returns the number of unique nonnull values.

An expression of any type. Note that COUNT_BIG does not support aggregate functions or subqueries in an expression.

Specifies that COUNT_BIG should count all rows to determine the total table row count to return. COUNT_BIG(*) takes no parameters and does not support the use of DISTINCT. COUNT_BIG(*) does not require an expression parameter because by definition, it does not use information about any particular column. COUNT_BIG(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.

OVER ( [ partition_by_clause ] [ order_by_clause ] )
The partition_by_clause divides the result set produced by the FROM clause into partitions to which the COUNT_BIG function is applied. If not specified, the function treats all rows of the query result set as a single group. The order_by_clause determines the logical order of the operation. See OVER Clause (Transact-SQL) for more information.

Return types



COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT_BIG (ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.

COUNT_BIG (DISTINCT expression) evaluates expression for each row in a group, and returns the number of unique, nonnull values.

COUNT_BIG is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when used with the OVER and ORDER BY clauses. See Deterministic and Nondeterministic Functions for more information.


See COUNT (Transact-SQL) for examples.

See also

Aggregate Functions (Transact-SQL)
COUNT (Transact-SQL)
int, bigint, smallint, and tinyint (Transact-SQL)
OVER Clause (Transact-SQL)