Arithmetic overflow error

If I run this code:

select 99999 * 9999

result = 999890001

If I run this code:

select 9999923 * 99999999

result:


Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

And If I run the following code:

select 922337299987987689745893 * 999999994564

result = 922337294974162127011191918541325652

Why am I getting this arithmetic overflow error within a specific range?

The rules that SQL Server uses to derive the type of literals are complex, with some odd behaviours that are maintained these days for backward compatibility. For example, the derived type may depend on whether simple parameterization is applied. There are other (undocumented) considerations as well.

With those caveats out of the way, the situation you face is one of the simpler ones. The untyped literals are typed as integer when they will fit in that data type. The data type of the result is as specified in * (Multiply) (Transact-SQL):


Returns the data type of the argument with the higher precedence.

For more information, see Data Type Precedence (Transact-SQL).

The two data types are both integer (with obviously the same precedence), so the result is also typed as integer. When the result does not fit, an error may be thrown (depending on settings).

When one of the literals is explicitly typed using CAST or CONVERT as (for example) bigint (which has a higher precedence than integer) the result type will be bigint, and no error occurs.

The two larger constants in the question are typed as numeric(24,0) and numeric(12,0) respectively. The result of multiplying them is typed as numeric(37,0) in accordance with the rules for e1 * e2 shown in Precision, Scale, and Length (Transact-SQL):

Result precision: p1 + p2 + 1 = 24 + 12 + 1 = 37
Result scale: s1 + s2 = 0 + 0 = 0

Result type: numeric (37,0)

StackExchange Database Administrators Q#151691, answer score: 10