Why does TSQL return the wrong value for POWER(2.,64.)?

From the online documentation:

POWER ( float_expression , y )  

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float

The implication is that whatever you pass as the first parameter is going to be implicitly cast to a float(53) before the function is executed. However, this is not (always?) the case.

If it were the case, it would explain the loss of precision:

Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

On the other hand, the literal 2. is type numeric…:

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

| (No column name) |
| :--------------- |
| numeric          |

dbfiddle here

…and the multiply operator returns the data type of the argument with the higher precedence.

It appears that on 2016 (SP1), all the precision is retained:

SELECT @@version;
GO

| (No column name)                                                                                                                                                                                                                                                  |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) <br>        Oct 28 2016 18:17:30 <br>        Copyright (c) Microsoft Corporation<br>        Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)<br> |

SELECT POWER(2.,64.);
GO

| (No column name)     |
| :------------------- |
| 18446744073709551616 |

dbfiddle here

…but on 2014 (SP2), they are not:

SELECT @@version;
GO

| (No column name)                                                                                                                                                                                                                             |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) <br>        Jun 17 2016 19:14:09 <br>        Copyright (c) Microsoft Corporation<br>        Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)<br> |

SELECT POWER(2.,64.);
GO

| (No column name)     |
| :------------------- |
| 18446744073709552000 |

dbfiddle here

The result of 2⁶⁴ is exactly representable in float (and real for that matter).

The problem arises when this precise result is converted back to numeric (the type of the first POWER operand).

Before database compatibility level 130 was introduced, SQL Server rounded float to numeric implicit conversions to a maximum of 17 digits.

Under compatibility level 130, as much precision as possible is preserved during the conversion. This is documented in the Knowledge Base article:

SQL Server 2016 improvements in handling some data types and uncommon operations

To take advantage of this in Azure SQL Database, you need to set the COMPATIBILITY_LEVEL to 130:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;

---

Workload testing is needed because the new arrangement is not a panacea. For example:

SELECT POWER(10., 38);

...ought to throw an error because 10³⁸ cannot be stored in numeric (maximum precision of 38). An overflow error results under 120 compatibility, but the result under 130 is:

99999999999999997748809823456034029568 -- (38 digits)

With a little math we can find a workaround. For odd n:

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

For even n:

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

One way to write that in T-SQL:

DECLARE @exponent INTEGER = 57;

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

Tested on SQL Server 2008, the result is 144115188075855872 instead of 144115188075855870.

This works all the way up to an exponent of 113. It looks like a NUMERIC(38,0) can store up to 2 ^ 126 so there isn't quite full coverage, but the formula could be split up into more pieces if necessary.