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).