In SQL Server, the COUNT_BIG() function returns the number of items found in a group. You can use it to find out how many rows are in a table or result set.

This function works similar to the COUNT() function. The difference is that COUNT() returns its result as an int, whereas COUNT_BIG() returns its result as a bigint.

Therefore COUNT_BIG() could come in handy if you expect your result set to have a very large number of rows (i.e. larger than 2,147,483,647).

Syntax

The syntax goes like this:

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

ALL applies the aggregate function to all values. This is the default value.

DISTINCT specifies that the function returns the number of unique nonnull values.

expression is an expression of any type. Aggregate functions and subqueries are not supported in the expression.

* specifies that all rows should be counted and returned, including duplicate rows, and rows that contain null values. COUNT(*) takes no parameters and does not support the use of DISTINCT. It also doesn’t require an expression parameter (because it doesn’t use information about any particular column).

OVER ( [ <partition_by_clause> ] divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

Example 1 – Basic Usage

Here’s a basic example showing how this function works:

USE WideWorldImportersDW;
SELECT COUNT_BIG(*) AS 'Row Count' 
FROM Fact.[Order];

Result:

+-------------+
| Row Count   |
|-------------|
| 231412      |
+-------------+

In this case there are 231412 rows in the Fact.[Order] table.

In this case, I could’ve used COUNT() to return the same result, because the row count is small enough for an int to handle.

Example 2 – A Larger Result Set

The real benefit of using COUNT_BIG() is when your result set is much larger than the previous example.

Example:

SELECT COUNT_BIG(*) AS 'Row Count' 
FROM ReallyBigTable;

Result:

+-----------------+
| Row Count       |
|-----------------|
| 9147483648      |
+-----------------+

In this case, the row count is so big that an int wouldn’t be able to handle it. Fortunately we can use COUNT_BIG(), because it returns its result as a bigint.

More Examples

For more examples, see How COUNT() Works in SQL Server. That article provides more examples than is listed here, all of which are also applicable to COUNT_BIG().

An Alternative: APPROX_COUNT_DISTINCT()

If you’re working with very large data sets, you might consider using APPROX_COUNT_DISTINCT() instead of COUNT_BIG(DISTINCT ) in some cases.

APPROX_COUNT_DISTINCT() returns an approximate value, rather than a precise value. However, it’s designed to be much more responsive than COUNT_BIG(), so it could be useful for times that responsiveness is more important than precision.

It’s designed to return unique, non-null values, so it would only be relevant for times where you would normally be using the DISTINCT clause with COUNT_BIG().

Also note that, at the time of writing APPROX_COUNT_DISTINCT() is in public preview status.