SQL Server: 数字字面量的计算

Question

我进行了一些浮点数计算测试，以最小化精度损失。我遇到了一个现象，在这里展示并希望得到解释。

当我写出：

print 1.0 / (1.0 / 60.0)

结果是

60.0024000960

当我写相同的公式并进行显式转换为float时。

print cast(1.0 as float) / (cast(1.0 as float) / cast(60.0 as float))

结果是

60

直到现在我一直认为带有小数位的数字文字会自动被视为具有适当精度的 float 值。将其强制转换为 real 和强制转换为 float 的结果相同。

• 是否有关于 SQL Server 如何评估数字文字的文档？
• 这些文字的数据类型是什么？
• 我真的需要将它们强制转换为 float 才能获得更好的精度吗（这听起来很讽刺）？
• 除了在公式中添加强制转换，是否有更简单的方法？

- VVS

Answer 1

SQL Server使用尽可能小的数据类型。

当你运行这个脚本时

SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')

你会发现SQL Server隐式地使用了NUMERIC(2, 1)数据类型。
除以60.0将结果转换为NUMERIC(8, 6)。
最终计算将结果转换为NUMERIC(17, 10)。

---

编辑 从SQL Server Books Online数据类型转换(Data Type Conversion)中获取。

在Transact-SQL语句中，有小数点的常量会自动转换为数字数据值，使用必要的最小精度和比例。例如，常量12.345会被转换为具有精度5和比例3的数字值。

- Lieven Keersmaekers

---

啊.. 直到现在我都不知道这个方法。非常感谢 :) - VVS

那么，没有其他方法可以得到预期的结果而不进行显式转换吗？是否有另一种方法告诉SQL Server给定的字面量是某种类型（例如float）？ - VVS

除了像你已经做的那样进行显式转换之外，我不知道还有其他方法。SQL Server总是将该值解释为数字。如果您自己向值添加精度，如1.000000000000000000000000000000000000，则可以稍微缓解这个问题。 - Lieven Keersmaekers

@Lieven: 你有任何证明“SQL Server总是将值解释为数字”的文档吗？ - VVS

@VSS：我已经添加了对文档的引用。 - Lieven Keersmaekers

Answer 2

我认为，为了今后类似情况的参考，应该理解幕后发生的事情。

带小数点的字面数值（不包括科学计数法）代表存储为最小可能 Decimal 类型的 Decimal 数据类型。与 Lieven Keersmaekers 的同样引用来自： https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal

在 Transact-SQL 语句中，具有小数点的常量会自动转换为数字数据值，使用所需的最小精度和比例。例如，常量 12.345 被转换为具有精度 5 和比例 3 的数字值。

小数点右侧的尾随零指定比例。小数点左侧的前导零将被忽略。

一些例子：

1.0  -> Decimal(2,1)
60.0 -> Decimal(3,1)
1.00 -> Decimal(3,2)
01.0 -> Decimal (2,1)

另一个需要考虑的问题是数据类型优先级。当运算符结合两个不同数据类型的表达式时，数据类型优先级规则指定将低优先级数据类型转换为高优先级数据类型。 还有一个需要考虑的问题是，如果我们对十进制类型执行算术运算，则结果十进制类型（即精度和比例）取决于两个操作数和操作本身。这在文档精度、比例和长度中有描述。 因此，括号中表达式的一部分。

( 1.0 / 60.0 ) is evaluated to 0.016666 and the resulting type is Decimal (8,6)

使用上述有关十进制表达式精度和比例的规则。此外，还使用银行家舍入法或四舍五入到偶数。重要的是注意十进制和浮点类型的不同舍入方式。如果我们继续这个表达式

1.0 / 0.016666 is evaluated to 60.002400096 and the resulting type is Decimal (17,10)

因此，差异的部分是由于十进制类型使用不同的舍入方式而不是浮点数。

根据上述规则，只需在括号内使用一个转换即可。根据数据类型优先级规则，每个其他文字都将升级为float。

1.0 / (1.0 / cast(60.0 as float))

还有一件非常重要的事情。即使这个浮点表达式也不能计算出精确的结果。只是为了让前端（如SSMS等）将值四舍五入到（我猜）6位精度，然后截断尾随的零。所以1.000001变成了1。

很简单，不是吗？

- f470071

Answer 3

是的，你经常需要将它们转换为浮点数以获得更好的精度。我对此的看法是：

在计算之前将小数转换为浮点数以获得更好的精度

- A-K

---

这篇文章的基本原理是有缺陷的。它基于一些任意的值。很容易找到支持十进制类型的反例。这些基于一个实证例子的印度主张本身就是陷阱。 - f470071

Answer 4

编写常量浮点表达式时，请尝试使用科学计数法： select (1.0E0 / (1.0E0 / 60.0E0)) 结果为60。

- user10918825