[!INCLUDEtsql-appliesto-ss2008-all-md]

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.

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> ] )  

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

DISTINCT
Specifies that COUNT_BIG returns the number of unique nonnull values.

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

bigint

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.

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