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