SQL SERVER – Difference Between Count and Count_Big

The difference between Count and COUNT_BIG includes result range and indexed-view requirements. Their row-counting rules are the same.

Interpret the difference between Count and COUNT_BIG

DECLARE @T table (Value int);
INSERT @T VALUES (1),(NULL),(1);
SELECT COUNT(*) AS RowsInt,COUNT_BIG(*) AS RowsBigint,
       COUNT(Value) AS NonNullValues,COUNT(DISTINCT Value) AS DistinctValues
FROM @T;

COUNT(*) counts every row, while COUNT(expression) excludes NULL. DISTINCT changes what is counted. The example returns 3, 3, 2 and 1. Counts are nonnegative despite the signed result datatype.

An int count can’t exceed 2,147,483,647. Session settings affect overflow behavior. Choose COUNT_BIG when the result can exceed that range.

Indexed views require determinism, schema binding, specified SET options and a unique clustered index. Grouped indexed views must contain COUNT_BIG(*). COUNT is not permitted in their definitions. COUNT_BIG alone does not make a view indexable.

Reference: Indexed-view aggregation requirements.

Decide what the total represents

Read the four sample outputs from left to right. The first two include all three input rows, including the row whose value is missing. The third includes only the two rows with a value. The fourth counts the single distinct non-null value, because both populated rows contain 1. Choosing a wider return type does not change any of those counting rules.

For a report, decide whether the total means records, populated values or unique populated values. A filter changes the rows that reach the aggregate. A join can change that row set too, so investigate duplicate matches when an unexpected total appears. Changing the function’s return type cannot repair an incorrect input set.

Check the result type expected by the application as well. A count that fits comfortably today may grow over time. When the possible result exceeds the int range, the wider aggregate provides the required capacity, but the receiving variable or application field must also accommodate it.

Treat an indexed view as a separate design decision. Review its complete definition and required settings rather than replacing one function and assuming that the view is eligible. The grouped aggregation requirement is only one part of that review.

Reference: Microsoft’s COUNT_BIG counting rules and return type.

Related reading

• SQL in Sixty Seconds video
• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

A wider count type is not complete indexed-view eligibility, it is one requirement among several.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.