SQL Server 2005 数字精度损失

2021-12-31 00:00:00 floating-point type-conversion casting floating-accuracy sql-server

调试一些金融相关的SQL代码，发现numeric(24,8)数学精度有一个奇怪的问题.

Debugging some finance-related SQL code found a strange issue with numeric(24,8) mathematics precision.

在您的 MSSQL 上运行以下查询，您将得到 A + B * C 表达式结果为 0.123457

Running the following query on your MSSQL you would get A + B * C expression result to be 0.123457

选择 A，乙，C，A + B * C从(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)

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

所以我们丢失了 2 个重要的符号.试图以不同的方式解决这个问题，我得到了中间乘法结果(零！)到数字(24,8)的转换可以正常工作.

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.

最后有一个解决方案.但我仍然有一个问题 - 为什么 MSSQL 会以这种方式运行，以及我的示例中实际发生了哪些类型转换?

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?

推荐答案

正如 float 类型的加法不准确一样，如果超出精度，decimal 类型的乘法可能会不准确(或导致不准确).请参阅数据类型转换和十进制和数字.

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.

既然你把NUMERIC(24,8)和NUMERIC(24,8)相乘，SQL Server只会检查类型而不是内容，它可能会尝试在无法保存所有 48 位精度(最大值为 38)时保存潜在的 16 位非十进制数字(24 - 8).将其中的两个组合起来，您会得到 32 个非十进制数字，而您只剩下 6 个十进制数字(38 - 32).

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

因此原始查询

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

减少到

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

同样，在 NUMERIC(24,8) 和 NUMERIC(38,6) 之间，SQL Server 将尝试保存潜在的 32 位非十进制数，因此A + D 减少到

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))

四舍五入后给你 0.123457.

相关文章