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

COUNT_BIG( [ * | [ ALL ] expression ]) OVER ( window-spec )

window-spec : see Syntax 2 instructions in the Remarks section below

COUNT_BIG returns a value of type BIGINT.

COUNT_BIG never returns the value NULL. If a group contains no rows, or if there are no non-null values of expression in a group, then COUNT_BIG returns 0.

It is recommended that you use the COUNT_BIG function when counting large result sets, the result might have more rows, or there is a possibility of overflow. Otherwise, use the COUNT function, which has a maximum value of 2147483647.

Syntax 2 represents usage as a window function in a SELECT statement. As such, elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition in WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

For more information about specifying a window specification in an OVER clause, see Window definition: inlining using the OVER clause and WINDOW clause.

• AVG function [Aggregate]
• SUM function [Aggregate]
• COUNT function [Aggregate]

The following statement returns each unique city, and the number of employees working in that city.

SELECT City, COUNT_BIG( * ) FROM Employees GROUP BY City;