SQL server 2005 numeric precision loss

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?

From stackoverflow Ilya Komakhin

• 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.

  From eed3si9n

• 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
  

  eed3si9n : That approach may not solve the issue of SQL Server chopping off the decimal parts. To save the decimal parts, it may be necessary to cast to @a and @b into double.

  kristof : Thanks, I will keep it in mind when working on some precision maths in SQL, So far I have not need to use it but it is good to now that there may be some issues to consider
  From kristof

• 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

  Chris Lively : See eed3si9n's answer. It was pretty clear.

  Rob : obviously I didn't think it was clear and that is why I asked this question.
  From Rob