In the process of producing the timings for the T/SQL function performance graph (over on the T/SQL functions page) I received an 'Arithmetic overflow error converting expression to data type int.' whilst executing the following code;

SELECT
   AVG(X) -- X is an INT
FROM
   #tmp

With 10,000 rows in #tmp (x = 1 to 10,000) the statement executes and produces the correct result. With 100,000 rows in #tmp (x = 1 to 100,000) the statement fails to execute and errors. Why? Checking the Avg function in BOL doesn't shed any light.

Fortunately I Googled lucky and found  Jezemine's dbforums.com post from October 2006. The error occurs if the sum of X (as calculated internally during the evaluation of AVG) exceeds the range of an Int, regardless of whether the datatype of x is tinyint, smallint or int. If x is a bigint then the failure point moves to when sum(x) exceeds the range of a bigint.

The question then is why is the internal datatype promotion to int managed, but not the step to bigint?

The code below demonstrates this issue. I've confirmed this behaviour on SQL 2000 (8.00.2039), 2005 (9.00.3054.00) and the February 2008 CTP for SQL 2008 (10.0.1300.13) (all Developer Edition).

/*
** INT example.
*/
CREATE TABLE #tmp (MyNumber INT NOT NULL)

INSERT #tmp (MyNumber) VALUES (2147483647) -- Int.Max
INSERT #tmp (MyNumber) VALUES (2147483647)

-- This will fail.
SELECT AVG(MyNumber) FROM #tmp

-- This will succeed.
SELECT AVG(CAST(MyNumber AS BIGINT)) FROM #tmp

DROP TABLE #tmp
go

/*
** BIGINT example.
*/
CREATE TABLE #tmp (MyNumber BIGINT NOT NULL)

INSERT #tmp (MyNumber) VALUES (9223372036854775807) -- BigInt.Max
INSERT #tmp (MyNumber) VALUES (9223372036854775807)

-- This will fail.
SELECT AVG(MyNumber) FROM #tmp

DROP TABLE #tmp
go