SQL server 2005 numeric precision loss

Question

Hello,

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?

Answer 1

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.

Answer 2

Following the logic pointed out by eed3si9n and what you said in your question it seems that the best approach when doing mathematics operations is to extract them into a function and additionally to specify precision after each operation,

It this case the function could look something like:

create function dbo.myMath(@a as numeric(24,8), @b as numeric(24,8), @c as numeric(24,8))
returns  numeric(24,8)
as
begin 
    declare @d as numeric(24,8)
    set @d = @b* @c
    return @a + @d
end

Answer 3

Then why does:

SELECT A + A * B 
FROM (SELECT CAST(0.12345678 AS NUMERIC(25,8)) AS A, 
CAST(0 AS NUMERIC(25,8)) AS B) T

give 0.123457 and not 0.1235