COUNT Under the Hood

COUNT() and COUNT_BIG() do the same thing: they return the total number of rows in your result set, or the number of rows-per-group with GROUP BY. Only COUNT_BIG() works when there are more than 2,147,483,647 rows in the table or group.

This is the rule in my style guide I break most often, usually because COUNT(1) is what my hands type. Muscle memory, and all that.

The return types

COUNT returns int.^[1] COUNT_BIG returns bigint.^[2]

An int tops out at 2,147,483,647.^[3] Ask COUNT for a number larger than that and you get:

What I assumed, and what the plan says

My assumption was that COUNT computes in int and overflows once it detects more than 2,147,483,647 rows. That is backwards.

The engine counts rows using a bigint value regardless of which function you specify. COUNT(*), COUNT(1) and COUNT_BIG(1) compile to the same internal aggregate, countstar, at the same estimated subtree cost. What COUNT adds is a Compute Scalar on top, narrowing the result back down:

COUNT_BIG has no such operator. It hands back the bigint the engine already had.

So the overflow is not a counter running out of room. It is a conversion at the end, discarding a value the engine computed correctly, because the function is designed to return a narrower type.

Verified on SQL Server 2019 (15.0.4480.2) and SQL Server 2025 (17.0.1125.2).

That reframes the choice. COUNT_BIG is not the expensive, cautious option you reach for on big tables. It is the one that skips an unnecessary conversion.

The documented NULL case

Microsoft documents a second failure mode: if both ARITHABORT and ANSI_WARNINGS are OFF, COUNT returns NULL instead of raising the overflow.^[1]

A NULL row count flowing into a comparison, a report total, or an IF branch is a worse outcome than an error.

Demonstrating it is difficult, because you need more than 2,147,483,647 rows and they have to come from somewhere. The narrowest table worth building contains a single bit or tinyint column (a single bit column still occupies a whole byte). A million of those rows measure 9 bytes each, plus 2 more for the row’s entry in the page slot array. Eleven bytes. An 8 KB page has 8,096 bytes free once its header is subtracted, so 736 rows fit, and 2,147,483,648 rows need 2,918,431 pages. That is 22.27 GB, and at a sustained 300,000 rows per second the insert runs for about two hours.

Tedious. That is a reason to find a cheaper test, not a reason to skip one.

You do not have to store the rows. A cross join produces them on demand, and 1,300 cubed is 2,197,000,000, which is 49,516,353 more than the limit:

With 1,300 rows in #n that returns 2,197,000,000 in 33 ms on SQL Server 2019 and 57 ms on 2025. Swap COUNT_BIG for COUNT and it raises Msg 8115. Set both session options OFF and run it again:

The documented behaviour holds on both versions.

Worth knowing: under sqlcmd, ANSI_WARNINGS and ARITHABORT both default to OFF on the instances I tested. The connection settings that produce a NULL row count rather than an error are not exotic.

Why it only took 33 milliseconds

Counting 2.197 billion rows in 33 milliseconds is equal to 66 billion rows per second. This is not what SQL Server actually did; the plan shows what it executed instead.

A Stream Aggregate counts the inner cross join of two copies of #n, 1,300 by 1,300, which is 1,690,000 rows. The outer aggregate multiplies that result across the third copy. 1,690,000 rows pass through the plan, not 2,197,000,000. The engine decomposed the aggregate rather than generating the product.

Which makes this a better demonstration of the earlier point than the one I had planned. The count was never held in a counter that filled up. It was computed correctly as a bigint, by an engine that never generated most of the rows, and then broken by a conversion on the way out.

Why not just use COUNT and fix it when it breaks

Because of where it breaks.

The count that overflows is rarely SELECT COUNT(1) FROM [dbo].[big_table] typed at a prompt. It is inside a procedure that has worked for six years, or a monitoring query, or a nightly check that compares a row count against a threshold. The table crosses 2.1 billion rows on an ordinary Tuesday and the failure surfaces somewhere unrelated to the count.

COUNT_BIG costs nothing to write and removes the entire category. There is no scenario where the bigint return is a problem and the int return is a benefit.

Where I have been inconsistent

I went looking through my own published scripts while writing this, and the habit is not evenly applied. The scripts in the Proving the Restore series use COUNT_BIG(1) in some places and COUNT(1) in others, in code written weeks apart.

None of those would ever overflow. They count backup sets and files, and the numbers are in the dozens. Which is the point: the inconsistency is invisible because the small cases work forever. The one that matters is the one you wrote the same way without thinking, against a table that grew.

So the rule is not “use COUNT_BIG when the number might be large.” You cannot reliably predict which of today’s small tables becomes tomorrow’s large one, and the judgement call costs more than the habit. The rule is to use it every time.

COUNT(1) versus COUNT(*)

Since it comes up whenever counting does: there is no performance difference between COUNT(1) and COUNT(*).

Both compile to the same countstar aggregate. COUNT(*) does not read every column, and COUNT(1) does not save the engine any work by naming a literal. The plans are identical.

COUNT(*) and COUNT(1) also mean something different from COUNT([some_column]), which counts non-NULL values in that column, and that difference is real. If you want the number of rows, do not name a nullable column.

The rule

Use COUNT_BIG(1).

Four extra characters, one fewer operator in the plan, and a class of failure you’ll never run into. I still type COUNT(1) by reflex; the rule exists because the reflex is wrong and I am not going to retrain it.

This is one of a series on the T-SQL conventions I actually use and why. The companion piece on functions that change a result’s type without saying so is ISNULL Truncates Your Replacement Value; COALESCE Doesn’t.

Have you had a COUNT overflow in production, and where did it surface? Bluesky or LinkedIn.

References

1. COUNT (Transact-SQL) - Microsoft Learn. States the int return type, the overflow error, and the documented NULL behaviour when ARITHABORT and ANSI_WARNINGS are both OFF. ↩ ↩
2. COUNT_BIG (Transact-SQL) - Microsoft Learn. The bigint-returning equivalent, otherwise identical in behaviour. ↩
3. int, bigint, smallint, and tinyint (Transact-SQL) - Microsoft Learn. The range limits behind the 2,147,483,647 boundary. ↩