为什么 TSQL 会为 POWER(2.,64.) 返回错误的值？

从在线文档：

POWER ( float_expression , y )  

论据

float_expression 是浮点类型或可以隐式转换为浮点类型的表达式

这意味着无论您作为第一个参数传递的任何内容都将在函数执行float(53) 之前被隐式转换为 a 。但是，情况并非（总是？）。

如果是这种情况，它将解释精度的损失：

将使用科学记数法的浮点值转换为十进制或数字仅限于精度为 17 位的值。任何精度高于 17 的值都舍入为零。

另一方面，字面2.量是类型numeric……：

DECLARE @foo sql_variant;
SELECT @foo = 2.;
SELECT SQL_VARIANT_PROPERTY(@foo, 'BaseType');
GO

| （无列名） |
| :--------------- |
| 数字 |

dbfiddle在这里

…并且乘法运算符返回具有更高优先级的参数的数据类型。

似乎在 2016 (SP1) 上保留了所有精度：

SELECT @@version;
GO

| （无列名） |
| :------------------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) <br> 2016 年 10 月 28 日 18:17:30 <br> 版权所有 (c) Microsoft Corporation<br> Windows Server 上的速成版（64 位） 2012 R2 标准 6.3 <X64>（内部版本 9600：）（管理程序）<br> |

SELECT POWER(2.,64.);
GO

| （无列名） |
| :-------------------- |
| 18446744073709551616 |

dbfiddle在这里

…但在 2014 (SP2) 上，它们不是：

SELECT @@version;
GO

| （无列名） |
| :------------------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------ |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) <br> 2016 年 6 月 17 日 19:14:09 <br> 版权所有 (c) Microsoft Corporation<br> Windows NT 上的速成版（64 位） 6.3 <X64>（内部版本 9600：）（管理程序）<br> |

SELECT POWER(2.,64.);
GO

| （无列名） |
| :-------------------- |
| 18446744073709552000 |

dbfiddle在这里

2 ⁶⁴float的结果在（和real就此而言）完全可以表示。

当这个精确的结果被转换回numeric（第一个POWER操作数的类型）时，问题就出现了。

在引入数据库兼容级别 130 之前，SQL Server将隐式转换舍入float到最多 17 位。numeric

在兼容级别 130 下，在转换过程中会保留尽可能多的精度。这记录在知识库文章中：

SQL Server 2016 在处理某些数据类型和不常见操作方面的改进

要在 Azure SQL 数据库中利用这一点，您需要将其设置COMPATIBILITY_LEVEL为 130：

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;

需要进行工作负载测试，因为新安排不是万能的。例如：

SELECT POWER(10., 38);

...应该抛出一个错误，因为 10 ³⁸无法存储numeric（最大精度为 38）。120兼容性下出现溢出错误，但130下的结果是：

99999999999999997748809823456034029568 -- (38 digits)

通过一点数学，我们可以找到一种解决方法。对于奇数n：

2 ^ n 
= 2 ^ (2k + 1)
= 2 * (2 ^ 2k)
= 2 * (2 ^ k) * (2 ^ k)

对于偶数n：

2 ^ n 
= 2 ^ (2k)
= 1 * (2 ^ 2k)
= 1 * (2 ^ k) * (2 ^ k)

在 T-SQL 中编写它的一种方法：

DECLARE @exponent INTEGER = 57;

SELECT (1 + @exponent % 2) * POWER(2., FLOOR(0.5 * @exponent)) * POWER(2., FLOOR(0.5 * @exponent));

在 SQL Server 2008 上测试，结果是 144115188075855872 而不是 144115188075855870。

这一直有效到 113 的指数。看起来 NUMERIC(38,0) 最多可以存储 2 ^ 126，所以没有完全覆盖，但如果需要，公式可以分成更多部分.

只是为了好玩，递归 CTE 解决方案：

with 
  prm (p, e) as           -- parameters, to evaluate: p**e
    (select 2, 64),       -- (2 ** 64)  
  pow (power, exp) as 
    (select cast(p as numeric(30,0)), 
            e
     from prm 
     union all 
     select cast(power * power * (case when exp % 2 = 0 then 1 else p end) 
                 as numeric(30,0)), 
            exp / 2 
     from prm, pow 
     where exp > 1 
    ) 
select power 
from pow 
where exp = 1 ;