Article ID: 163069
Article Last Modified on 2/25/2004
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q163069
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.
Additional query words: prodsql sp sp5
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:
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
Keywords: kbbug kbprb kbpending kbusage KB163069
