SQL Server精确度疯狂 [英] Sql Server Precision Crazyness

问题描述

我在使用sql server精度时遇到问题.

I am having an issue with sql server precision.

我有以下查询:

DECLARE @A numeric(30,10)
DECLARE @B numeric(30,10)
SET @A = 20.225
SET @B = 53.3875
SELECT @A * @B

DECLARE @A1 numeric(30,14)
DECLARE @B1 numeric(30,14)
SET @A1 = 20.225
SET @B1 = 53.3875
SELECT @A1 * @B1

DECLARE @A3 numeric(30,15)
DECLARE @B3 numeric(30,15)
SET @A3 = 20.225
SET @B3 = 53.3875
SELECT @A3 * @B3

DECLARE @A2 numeric(20,15)
DECLARE @B2 numeric(20,15)
SET @A2 = 20.225
SET @B2 = 53.3875
SELECT @A2 * @B2

DECLARE @A4 float
DECLARE @B4 float
SET @A4 = 20.225
SET @B4 = 53.3875
SELECT @A4 * @B4

分别产生以下结果:

1079.762188

1079.762188

1079.762188

1079.762188

1079.7621875

1079.7621875

1079.762187500000000000000000000

1079.762187500000000000000000000

1079.7621875

1079.7621875

正确的答案是:1079.7621875.

The correct answer is: 1079.7621875.

我不明白为什么，当类型具有相同的签名时，它们将失去精度.另外，为什么从30,14变为30,15可以解决精度问题?还有，为什么20,15的小数位数比30,15小得多?

I do not understand why, when the types have the same signature they are losing precision. Also, why does going from 30,14 to 30,15 fix the precision problem? Also, why does 20,15 have so many more decimals than 30,15?

我已阅读本文 http://msdn. microsoft.com/en-us/library/ms190476(SQL.90).aspx ，我认为应该没问题，因为我的变量具有相同的精度.

I have read this article http://msdn.microsoft.com/en-us/library/ms190476(SQL.90).aspx and I think I should be fine because my variables have the same precision.

任何帮助将不胜感激！

推荐答案

答案在于计算机如何在内部表示数字.根据您使用的精度，SQL Server将分配5、9、13或17个字节来表示您的数字(请参见

The answer lies in how computers represent numbers internally. Depending on the precision you use, SQL Server will allocate 5, 9, 13 or 17 bytes to represent your number (see http://msdn.microsoft.com/en-us/library/ms187746(v=SQL.90).aspx) So, for example when you moved from precision 30 to precision 20, the internal representation moved from 17 bytes to 13 bytes. How you set the scale on a 17 byte number versus a 13 byte number where a greater proportion of the number representation is dedicated to scale (15/30 = 0.5, 15/20 = 0.75, changes the rounding behavior. There is no perfect answer. The number types we have are good enough for most applications but sometimes you'll get strange artifacts like you're seeing due to the way we've compromised in representing numbers in computers.

顺便说一句，要非常小心浮点类型.它们仅粗略地近似于数字，并且在数量上使用时，会给您非常错误的结果.当一次计算中使用的浮点数不超过20个时，它们对于大多数科学应用而言都是极好的.当大量使用时，假设在sum(column_name)中添加一百万个浮点数，您将得到垃圾.下面的演示:

As an aside, be very, very careful of float types. They only roughly approximate numbers and will give you very wrong results when used in quantity. They are superb for most scientific applications when no more than about 20 floating point numbers are used in one calculation. When used in quantity, say adding 1 million floating point numbers in a sum(column_name) you will get garbage. Demonstration below:

DECLARE @f FLOAT
DECLARE @n NUMERIC(20,10)
DECLARE @i INT

SET @f = 0
SET @n = 0
SET @i = 0

WHILE @i < 1000000
BEGIN
    SET @f = @f + 0.00000001
    SET @n = @n + 0.00000001
    SET @i = @i + 1
END

SELECT @n as [Numeric], @f as [Float]

这为我在SQL Server 2008上提供了以下答案.

This gives me the following answer on SQL Server 2008.

Numeric      Float
0.0100000000    0.00999999999994859

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