SQL Server 2005 数值精度损失

2022-01-13 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从(选择 CAST(0.12345678 作为数字(24,8))作为 A，CAST(0 作为数字(24,8))作为 B，铸造(500 作为数字(24,8))作为 C) T

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?

推荐答案

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

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.

which gives you 0.123457 after rounding.

相关文章