Por que o TSQL retorna o valor errado para POWER(2.,64.)?

Da documentação online :

POWER ( float_expression , y )  

Argumentos

float_expression É uma expressão do tipo float ou de um tipo que pode ser convertido implicitamente em float

A implicação é que tudo o que você passar como o primeiro parâmetro será convertido implicitamente em a float(53) antes que a função seja executada. No entanto, este não é (sempre?) o caso .

Se fosse o caso, explicaria a perda de precisão:

A conversão de valores float que usam notação científica para decimal ou numérico é restrita apenas a valores de precisão de 17 dígitos. Qualquer valor com precisão superior a 17 voltas para zero.

Por outro lado, o literal 2.é do tipo numeric…:

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

| (Sem nome de coluna) |
| :--------------- |
| numérico |

dbfiddle aqui

…e o operador de multiplicação retorna o tipo de dados do argumento com a precedência mais alta .

Parece que em 2016 (SP1), toda a precisão é mantida:

SELECT @@version;
GO

| (Sem nome de coluna) |
| :------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) <br> 28 de outubro de 2016 18:17:30 <br> Copyright (c) Microsoft Corporation<br> Express Edition (64 bits) no Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)<br> |

SELECT POWER(2.,64.);
GO

| (Sem nome de coluna) |
| :------------------- |
| 18446744073709551616 |

dbfiddle aqui

…mas em 2014 (SP2), eles não são:

SELECT @@version;
GO

| (Sem nome de coluna) |
| :------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------ |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) <br> 17 de junho de 2016 19:14:09 <br> Copyright (c) Microsoft Corporation<br> Express Edition (64 bits) no Windows NT 6.3 <X64> (Build 9600: ) (Hipervisor)<br> |

SELECT POWER(2.,64.);
GO

| (Sem nome de coluna) |
| :------------------- |
| 18446744073709552000 |

dbfiddle aqui

O resultado de 2 ⁶⁴ é exatamente representável em float(e realpara esse assunto).

O problema surge quando esse resultado preciso é convertido novamente para numeric(o tipo do primeiro POWERoperando).

Antes da introdução do nível de compatibilidade de banco de dados 130, o SQL Server arredondava floatpara numericconversões implícitas para um máximo de 17 dígitos.

No nível de compatibilidade 130, a maior precisão possível é preservada durante a conversão. Isso está documentado no artigo da Base de Conhecimento:

Melhorias do SQL Server 2016 na manipulação de alguns tipos de dados e operações incomuns

Para aproveitar isso no Banco de Dados SQL do Azure, você precisa definir COMPATIBILITY_LEVELcomo 130:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;

O teste de carga de trabalho é necessário porque o novo arranjo não é uma panacéia. Por exemplo:

SELECT POWER(10., 38);

...deve gerar um erro porque 10 ³⁸ não pode ser armazenado numeric(precisão máxima de 38). Um erro de estouro resulta em compatibilidade de 120, mas o resultado em 130 é:

99999999999999997748809823456034029568 -- (38 digits)

Com um pouco de matemática, podemos encontrar uma solução alternativa. Para ímpar n:

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

Para mesmo n:

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

Uma maneira de escrever isso em T-SQL:

DECLARE @exponent INTEGER = 57;

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

Testado no SQL Server 2008, o resultado é 144115188075855872 em vez de 144115188075855870.

Isso funciona até um expoente de 113. Parece que um NUMERIC(38,0) pode armazenar até 2 ^ 126, então não há uma cobertura completa, mas a fórmula pode ser dividida em mais partes, se necessário .

Apenas por diversão, uma solução CTE recursiva:

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 ;