SQL SERVER 中 Decimal 相乘，结果精度过高时的截断规律

精度、小数位数和长度 (Transact-SQL)

运算      结果精度        结果小数位数
--------- --------------- ------------
e1 * e2   p1 + p2 + 1     s1 + s2
* 结果精度和小数位数的绝对最大值为 38。 当结果精度大于 38 时，相应的小数位数会减少，以避免结果的整数部分被截断。

MSDN只是笼统地说截小数。而在参与帖子sql小数相乘出现零值问题 讨论时测试发现，整数也是可以截断的，但是并不是按整数的有效位数进行处理，而是有其它的规律。

一、测试下来截断方式其实有三种

A)小数位足够截，直接截小数。

条件：(s1+s2) > (p1+p2+1-38)
结果：p3 = 38, s3 = (s1+s2) - (p1+p2+1-38)

B)小数位不够截并且小数位不到6，保留全部小数位。

条件：((s1+s2) <= (p1+p2+1-38)) AND ((s1+s2) < 6)
结果：p3 = 38, s3 = s1+s2

C)小数位不够截并且小数位超过6，固定保留6个小数位。

条件：((s1+s2) <= (p1+p2+1-38)) AND ((s1+s2) >= 6)
结果：p3 = 38, s3 = 6

按照wuyongjun198提供的例子，重新归纳。处理优先级调整成B、C、A——2015年5月29日

B)小数位不到6，保留全部小数位。

条件：(s1+s2) < 6
结果：p3 = 38, s3 = s1+s2

C)小数位截去后不足6，固定保留6个小数位。

条件：(s1+s2) - (p1+p2+1-38) < 6
结果：p3 = 38, s3 = 6

A)小数位截去后至少6，直接截小数。

条件：(s1+s2) - (p1+p2+1-38) >> 6
结果：p3 = 38, s3 = (s1+s2) - (p1+p2+1-38)

 测试

SELECT SQL_VARIANT_PROPERTY(v,'BaseType') BaseType,
       SQL_VARIANT_PROPERTY(v,'Precision') [Precision],
       SQL_VARIANT_PROPERTY(v,'Scale') Scale,
       v
  FROM (
        -- 方式A
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,23)) * cast(1.0 as decimal(38,23)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,23)) * cast(1.0 as decimal(28,23)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,15)) * cast(1.0 as decimal(28,15)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,16)) * cast(1.0 as decimal(28,16)) ) AS v
        -- 方式B
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,0)) * cast(1.0 as decimal(38,0)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,0)) * cast(1.0 as decimal(38,1)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,1)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,2)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,3)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,4)) ) AS v
        -- 方式C
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,5)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,1)) * cast(1.0 as decimal(38,6)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(28,3)) * cast(1.0 as decimal(28,6)) ) AS v
        -- 方式C补充
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,19)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,20)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,21)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,22)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,23)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,24)) ) AS v
        -- 方式A补充
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,25)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,26)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(1.0 as decimal(38,20)) * cast(1.0 as decimal(38,27)) ) AS v
       ) T

方式A

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          7       1.0000000
decimal     38          27      1.000000000000000000000000000
decimal     38          11      1.00000000000
decimal     38          13      1.0000000000000

1. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 23+23 = 46
   s3 = 46-39 = 7
2. p1+p2+1-38 = 28+28+1-38 = 19
   s1+s2 = 23+23 = 46
   s3 = 46-19 = 27
3. p1+p2+1-38 = 28+28+1-38 = 19
   s1+s2 = 15+15 = 30
   s3 = 30-19 = 11
4. p1+p2+1-38 = 28+28+1-38 = 19
   s1+s2 = 16+16 = 32
   s3 = 30-19 = 13
   

方式B

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          0       1
decimal     38          1       1.0
decimal     38          2       1.00
decimal     38          3       1.000
decimal     38          4       1.0000
decimal     38          5       1.00000

1. s3 = s1+s2 = 0+0 = 0
2. s3 = s1+s2 = 0+1 = 2
3. s3 = s1+s2 = 1+1 = 2
4. s3 = s1+s2 = 1+2 = 3
5. s3 = s1+s2 = 1+3 = 4
6. s3 = s1+s2 = 1+4 = 5

方式C

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000

1. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 1+5 = 6
   s3 = 6-39 < 6 → s3 = 6
2. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 1+6 = 7
   s3 = 7-39 < 6 → s3 = 6
3. p1+p2+1-38 = 28+28+1-38 = 19
   s1+s2 = 3+6 = 9
   s3 = 9-19 < 6 → s3 = 6

方式C补充

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000
decimal     38          6       1.000000

1. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+19 = 39
   s3 = 39-39 < 6 → s3 = 6
2. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+20 = 40
   s3 = 40-39 < 6 → s3 = 6
3. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+21 = 41
   s3 = 41-39 < 6 → s3 = 6
4. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+22 = 42
   s3 = 42-39 < 6 → s3 = 6
5. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+23 = 43
   s3 = 43-39 < 6 → s3 = 6
6. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+24 = 44
   s3 = 44-39 < 6 → s3 = 6

方式A补充

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          6       1.000000
decimal     38          7       1.0000000
decimal     38          8       1.00000000

1. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+25 = 45
   s3 = 45-39 = 6
2. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+26 = 46
   s3 = 46-39 = 7
3. p1+p2+1-38 = 38+38+1-38 = 39
   s1+s2 = 20+27 = 47
   s3 = 47-39 = 8

二、关于有效位个数

有意思的是，当不需要截断时（p1+p2+1<=38），总有效位数只能利用到37。
而进行截断时，38个有效位都能利用起来。

SELECT SQL_VARIANT_PROPERTY(v,'BaseType') BaseType,
       SQL_VARIANT_PROPERTY(v,'Precision') [Precision],
       SQL_VARIANT_PROPERTY(v,'Scale') Scale,
       v
  FROM (
        SELECT CONVERT(sql_variant, cast(POWER(10.0,18) as decimal(20,0)) * cast(POWER(10.0,19) as decimal(20,0)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(POWER(10.0,17) as decimal(38,1)) * cast(POWER(10.0,18) as decimal(38,1)) ) AS v
        UNION ALL
        SELECT CONVERT(sql_variant, cast(POWER(10.0,15) as decimal(38,6)) * cast(POWER(10.0,16) as decimal(38,6)) ) AS v
       ) T

BaseType    Precision   Scale   v
----------- ----------- ------- ---------------------------------------
decimal     38          0       10000000000000000000000000000000000000
decimal     38          2       100000000000000000000000000000000000.00
decimal     38          6       10000000000000000000000000000000.000000

三、关于溢出

只要对整数有截断（方式B、C），就存在溢出的可能。
下面的语句都会出溢出错误。

SELECT CONVERT(sql_variant, cast(POWER(10.0,19) as decimal(20,0)) * cast(POWER(10.0,19) as decimal(20,0)) ) AS v
SELECT CONVERT(sql_variant, cast(POWER(10.0,18) as decimal(38,1)) * cast(POWER(10.0,18) as decimal(38,1)) ) AS v
SELECT CONVERT(sql_variant, cast(POWER(10.0,16) as decimal(38,6)) * cast(POWER(10.0,16) as decimal(38,6)) ) AS v