SQL Server COUNT_BIG() Function

COUNT_BIG() returns the number of rows or non-NULL values as a bigint. It behaves like COUNT(), but COUNT() returns int and can overflow when a count exceeds 2,147,483,647. Use COUNT_BIG() when a result may exceed that limit. See Microsoft’s COUNT_BIG reference.

Syntax

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

• COUNT_BIG(*) counts every row, including rows that contain NULL values.
• COUNT_BIG(expression) counts non-NULL results of the expression.
• COUNT_BIG(DISTINCT expression) counts distinct, non-NULL expression values.

The return type is always bigint.

Count all rows

Use COUNT_BIG(*) when the number of rows may exceed the int range:

SELECT COUNT_BIG(*) AS row_count
FROM dbo.LargeTable;

It also works per group:

SELECT CustomerID, COUNT_BIG(*) AS order_count
FROM dbo.Orders
GROUP BY CustomerID;

Do not rely on CAST(COUNT(*) AS bigint) to prevent overflow: COUNT(*) returns int before the cast is applied. Use COUNT_BIG(*) for a bigint count. See SQL Server Error 8115: Arithmetic Overflow.