SQL Server 2005数字精度损失 [英] SQL server 2005 numeric precision loss

问题描述

调试一些财务相关的SQL代码发现一个奇怪的问题与数字（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

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 + 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?

推荐答案

添加浮点类型不准确，如果超过精度，小数类型的乘法可能不准确（或导致不准确）。请参见数据类型转换和< a href =http://msdn.microsoft.com/en-us/library/ms187746%28SQL.90%29.aspx =nofollow noreferrer>十进制和数字。

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

Thus the original query

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 。

这篇关于SQL Server 2005数字精度损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！