The COUNT_BIG function

SQL Server Development: The COUNT_BIG function is used much like the COUNT function to return the number of items in a group. COUNT_BIG returns a bigint data type and has a maximum value returned of 9,223,372,036,854,775,807. COUNT returns an int data type and has a maximum value returned of 2,147,483,647. COUNT_BIG is useful for rowcounts of really large tables like those commonly found in data warehouses.

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

COUNT_BIG(*) returns the number of items in a group including 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(ALL *) will cause a syntax error.
COUNT_BIG(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values. COUNT_BIG(DISTINCT *) will cause a syntax error.

Here is a simple example of each: