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