在算术运算的上下文中理解精度和比例

让我们分解一下，仔细看看除法算术运算符的细节。这是 MSDN 对除法运算符的结果类型的描述：

结果类型

返回具有更高优先级的参数的数据类型。有关详细信息，请参阅数据类型优先顺序 (Transact-SQL)。

如果整数被除数除以整数除数，则结果是一个整数，结果的任何小数部分都被截断了。

我们知道那@big_number是一个DECIMAL. SQL Server 转换1成什么数据类型？它将它转换为INT. 我们可以通过以下方式确认这一点SQL_VARIANT_PROPERTY()：

SELECT
      SQL_VARIANT_PROPERTY(1, 'BaseType')   AS [BaseType]  -- int
    , SQL_VARIANT_PROPERTY(1, 'Precision')  AS [Precision] -- 10
    , SQL_VARIANT_PROPERTY(1, 'Scale')      AS [Scale]     -- 0
;

对于踢球，我们还可以1将原始代码块中的 替换为显式键入的值，DECLARE @one INT = 1;并确认我们得到相同的结果。

所以我们有一个DECIMAL和一个INT。由于DECIMAL具有比更高的数据类型优先级INT，我们知道除法的输出将被转换为DECIMAL。

那么问题出在哪里呢？

问题在于输出中的比例DECIMAL。以下是有关 SQL Server 如何确定算术运算结果的精度和小数位数的规则表：

Operation                              Result precision                       Result scale *
-------------------------------------------------------------------------------------------------
e1 + e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 - e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 * e2                                p1 + p2 + 1                            s1 + s2
e1 / e2                                p1 - s1 + s2 + max(6, s1 + p2 + 1)     max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2   max(s1, s2) + max(p1-s1, p2-s2)        max(s1, s2)
e1 % e2                                min(p1-s1, p2 -s2) + max( s1,s2 )      max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result 
  precision is greater than 38, the corresponding scale is reduced to prevent the 
  integral part of a result from being truncated.

下面是我们在这个表中的变量：

e1: @big_number, a DECIMAL(38, 0)
-> p1: 38
-> s1: 0

e2: 1, an INT
-> p2: 10
-> s2: 0

e1 / e2
-> Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 + max(6, 11) = 49
-> Result scale:                    max(6, s1 + p2 + 1) =      max(6, 11) = 11

根据上表中的星号注释， a可以具有的最大精度DECIMAL为 38 。所以我们的结果精度从 49 降低到 38，并且“相应的小数位数减少以防止结果的整数部分被截断”。从这个评论中不清楚比例是如何减少的，​​但我们知道这一点：

根据表中的公式，将两个 s 相除后的最小可能小数位数DECIMAL是 6。

因此，我们最终得到以下结果：

e1 / e2
-> Result precision: 49 -> reduced to 38
-> Result scale:     11 -> reduced to 6  

Note that 6 is the minimum possible scale it can be reduced to. 
It may be between 6 and 11 inclusive.

这如何解释算术溢出

现在答案很明显了：

我们部门的输出被转换为DECIMAL(38, 6)，DECIMAL(38, 6)不能容纳 10 ³⁷。

有了这个，我们可以通过确保结果适合来构建另一个成功的部门DECIMAL(38, 6)：

DECLARE @big_number    DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million   INT           = '1' + REPLICATE(0, 6);

PRINT @big_number / @one_million;

结果是：

10000000000000000000000000000000.000000

注意小数点后的 6 个零。我们可以确认结果的数据类型是DECIMAL(38, 6)通过使用SQL_VARIANT_PROPERTY()如上：

DECLARE @big_number   DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million  INT           = '1' + REPLICATE(0, 6);

SELECT
      SQL_VARIANT_PROPERTY(@big_number / @one_million, 'BaseType')  AS [BaseType]  -- decimal
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Precision') AS [Precision] -- 38
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Scale')     AS [Scale]     -- 6
;

一个危险的解决方法

那么我们如何绕过这个限制呢？

好吧，这当然取决于您进行这些计算的目的。您可能会立即跳转到的一种解决方案是将您的数字转换为FLOAT用于计算，然后DECIMAL在完成后将它们转换回。

这在某些情况下可能有效，但您应该仔细了解这些情况是什么。众所周知，将数字相互转换FLOAT是危险的，可能会给您带来意想不到的或不正确的结果。

在我们的例子中，将 10 ³⁷与10 37 相互转换FLOAT得到的结果完全错误：

DECLARE @big_number     DECIMAL(38,0)  = '1' + REPLICATE(0, 37);
DECLARE @big_number_f   FLOAT          = CAST(@big_number AS FLOAT);

SELECT
      @big_number                           AS big_number      -- 10^37
    , @big_number_f                         AS big_number_f    -- 10^37
    , CAST(@big_number_f AS DECIMAL(38, 0)) AS big_number_f_d  -- 9999999999999999.5 * 10^21
;

你有它。小心分开，我的孩子们。