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
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(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)
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
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) (
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];
int int int int
SELECT SQL_VARIANT_PROPERTY(922337299987987689745893, 'BaseType') AS [Type], SQL_VARIANT_PROPERTY(922337299987987689745893, 'Precision') AS [Precision], SQL_VARIANT_PROPERTY(922337299987987689745893, 'Scale') AS [Scale];
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];
numeric 37 0
For the query getting the error, convert one to
CONVERT(BIGINT, 9999923). For example:
SELECT CONVERT(BIGINT, 9999923) * 99999999
Our Awesome Free Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor
- Online MD5 Hash Generator
- Online SHA-1, SHA-256, SHA-512 Generator
- Online Base64 Encoder/Decoder
- Online CRC-32 Calculator
- Online Triple DES Encryptor/Decryptor
- Best World Clocks
- Database Administration Tutorials
- Programming Tutorials & IT News
- Linux & DevOps World
- Entertainment & General News
- All the Free, Online Tools you need