PRB: Multiplying Numeric Datatypes May Yield Lower Precision (163069)

SYMPTOMS

When you multiply two highly precise numeric datatypes, you may not receive the expected precision in the result. For example, consider the statements that follow:

   declare @num1 numeric(28,8),@num2 numeric(28,8),@num3 numeric(28,8)
   select @num1=1.0,@num2=0.12345678
   select @num3=@num1*@num2
   select @num3

The expected value is 0.12345678. However, in SQL Server 7.0 and SQL Server 2000, the value that is returned is 0.12345700, and on SQL Server 6.5 the value returned is 0.12345600.

MORE INFORMATION

According to the design specifications that apply when two numeric values are multiplied, the multiplication of two number with precision p1 and p2 and scale s1 and s2 results in a number with precision p1+p2+1 and scale s1+s2. However, you must be very careful when you perform operations like these. The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

For more information, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_8rc5.asp

To workaround this problem, you can lower the precision of the numeric variables so that the combined precision does not exceed 38. As a result, the scale is kept intact and the output is accurate. To do so, use the following code:

declare @num1 numeric(19,8),@num2 numeric(19,8),@num3 numeric(19,8) 
select @num1=1.0,@num2=0.12345678 
select @num3=@num1*@num2 
select @num3