SQL Server POWER function

Question

Using SQL Server 2008 R2 when I enter the following query: ``` SELECT CAST(POWER(2.0, 63.0) AS BIGINT); ``` Which yields the result: ``` 9223372036854775800 ``` However, using the Windows desktop calculator and raising 2 to the 63 yields: ``` 9223372036854775807 ``` Can someone please explain the difference -- or is there some internal conversion that SQL Server is doing? ... or am I missing something else?

Accepted Answer

The range of `BIGINT`in MS Sql Server is:

```
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
```

And your calculator is giving you the wrong number, because 2^63 can't have an odd number for its right-most digit.

The `POWER` function in SQL Server (http://technet.microsoft.com/en-us/library/ms174276.aspx), returns the same type as its first argument.

The correct way to write this query is:

```
DECLARE @foo REAL = 2.0
SELECT CAST(POWER( @foo, 63.0 ) AS BIGINT)
```

By which, you will get `Arithmetic overflow error converting expression to data type bigint.` error message. And about the reason that's http://www.extremeoptimization.com/resources/Articles/FPDotNetConceptsAndFormats.aspx

And regarding the question of why `POWER` function is returning a wrong number? As @simonatrcl mentioned in his answer, there is arithmetic problems with floating-point numbers which sometimes result in invalid result. You can read about floating-point numbers and the problems with them here:

http://www.extremeoptimization.com/resources/Articles/FPDotNetConceptsAndFormats.aspx

You can also check the boundaries for integer types in MS Sql Server here: http://technet.microsoft.com/en-us/library/ms187745.aspx

SQL Server POWER function

Solution

Problem