Arithmetic overflow error converting expression to data type int

Today I was troubleshooting a query that was not returning expected records. In so doing, I decided to get a count of the records SQL Server 2005 was returning. Here is the query I used:

SELECT count(*)
FROM jdr WITH (NOLOCK)
INNER JOIN HR WITH (NOLOCK)
on hr.IB_ID = jdr.ib_id

Upon execution, I got the following SQL Server error:

Arithmetic overflow error converting expression to data type int.

As it turns out, the resulting count(*) was in fact out of range for an int data type.
See this SQL Books online article .

Using COUNT_BIG(*) instead of COUNT(*), I avoided the error:

SELECT COUNT_BIG(*)
FROM jdr WITH (NOLOCK)
INNER JOIN HR WITH (NOLOCK)
on hr.IB_ID = jdr.ib_id

--333,514,544,659

So the moral of the story is that if you are counting more than ~2 million records, better to use COUNT_BIG(*)


Happy TSQLing
SQLPoet