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?

Answer 1

The range of BIGINTin 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

Answer 2

Power will be returning a FLOAT. Floating point numbers are not accurate beyond certain limits, and will drop a bit of accuracy (if you've ever has a negative 0 problem you'll know what I mean!).

That's what you're getting here...