Arithmetic overflow error

Question

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?

Answer 1

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)

Answer 2

In Microsoft documentation, it is said that

The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Since yours inputs are considered as int, the result is supposed to be a int, meaning below 2 147 483 647. If it's bigger, it will indeed raise an error.

If you cast one of your inputs as another datatype such as bigint, it will not fail :

select 9999923 * cast(99999999 as bigint)
-- 999992290000077

Answer 3

This is due the default datatype that each literal number is interpreted as being.

The numbers in the first two queries are being assigned the datatype of INT. The large number in the third query is being assigned a datatype of NUMERIC(24, 0) (NUMERIC and DECIMAL are synonyms).

To see this, you can use the SQL_VARIANT_PROPERTY function to get the resulting datatype details of the literals (** Please note: SQL_VARIANT_PROPERTY might not return the initial type chosen by SQL Server in all cases ):

SELECT SQL_VARIANT_PROPERTY(99999, 'BaseType') AS [TypeFor99999],
       SQL_VARIANT_PROPERTY(9999, 'BaseType') AS [TypeFor9999],
       SQL_VARIANT_PROPERTY(9999923, 'BaseType') AS [TypeFor9999923],
       SQL_VARIANT_PROPERTY(99999999, 'BaseType') AS [TypeFor99999999];

Returns:

int   int   int   int

And then:

SELECT SQL_VARIANT_PROPERTY(922337299987987689745893, 'BaseType') AS [Type],
       SQL_VARIANT_PROPERTY(922337299987987689745893, 'Precision') AS [Precision],
       SQL_VARIANT_PROPERTY(922337299987987689745893, 'Scale') AS [Scale];

Returns:

numeric 24  0

And the full expression gives you:

SELECT SQL_VARIANT_PROPERTY(922337299987987689745893 * 999999994564, 'BaseType') AS [Type],
       SQL_VARIANT_PROPERTY(922337299987987689745893 * 999999994564, 'Precision') AS [Precision],
       SQL_VARIANT_PROPERTY(922337299987987689745893 * 999999994564, 'Scale') AS [Scale];

Returns:

numeric 37  0

For the query getting the error, convert one to BIGINT via CONVERT(BIGINT, 9999923). For example:

SELECT CONVERT(BIGINT, 9999923) * 99999999

Returns:

999992290000077