Debugging some finance-related SQL code found a strange issue with numeric(24,8) mathematics precision.
Running the following query on your MSSQL you would get A + B * C expression result to be 0.123457
SELECT A, B, C, A + B * C FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A, CAST(0 AS NUMERIC(24,8)) AS B, CAST(500 AS NUMERIC(24,8)) AS C ) T
So we have lost 2 significant symbols. Trying to get this fixed in different ways i got that conversion of the intermediate multiplication result (which is Zero!) to numeric (24,8) would work fine.
And finally a have a solution. But still I hace a question - why MSSQL behaves in this way and which type conversions actually occured in my sample?
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2. In SQL Server, the default maximum precision of numeric and decimal data types is 38.
In TSQL, you can specify two different sizes for float, 24 or 53. This will set the precision to 7 or 15 digits respectively. Show activity on this post. As a general rule, you can't specify the number of digits after the decimal point for a floating-point number.
Numeric precision refers to the maximum number of digits that are present in the number. ie 1234567.89 has a precision of 9. Numeric scale refers to the maximum number of decimal places. ie 123456.789 has a scale of 3. Thus the maximum allowed value for decimal(5,2) is 999.99.
Just as addition of the float type is inaccurate, multiplication of the decimal types can be inaccurate (or cause inaccuracy) if you exceed the precision. See Data Type Conversion and decimal and numeric.
Since you multiplied NUMERIC(24,8)
and NUMERIC(24,8)
, and SQL Server will only check the type not the content, it probably will try to save the potential 16 non-decimal digits (24 - 8) when it can't save all 48 digits of precision (max is 38). Combine two of them, you get 32 non-decimal digits, which leaves you with only 6 decimal digits (38 - 32).
Thus the original query
SELECT A, B, C, A + B * C
FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A,
CAST(0 AS NUMERIC(24,8)) AS B,
CAST(500 AS NUMERIC(24,8)) AS C ) T
reduces to
SELECT A, B, C, A + D
FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A,
CAST(0 AS NUMERIC(24,8)) AS B,
CAST(500 AS NUMERIC(24,8)) AS C,
CAST(0 AS NUMERIC(38,6)) AS D ) T
Again, between NUMERIC(24,8)
and NUMERIC(38,6)
, SQL Server will try to save the potential 32 digits of non-decimals, so A + D
reduces to
SELECT CAST(0.12345678 AS NUMERIC(38,6))
which gives you 0.123457
after rounding.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With