Microsoft KB Archive/163069

= PRB: Multiplying Numeric Datatypes May Yield Lower Precision =

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:

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

Keywords: kbbug kbprb kbpending kbusage KB163069

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.