SQL Server POWER() Function

POWER() raises a numeric expression to a specified power. Its return type depends on the base expression, so an integer base produces an integer result and can overflow. See Microsoft’s POWER() reference.

Syntax

POWER(float_expression, y)

• float_expression: the base value; it must be float or convertible to float.
• y: the exponent, which can be an exact or approximate numeric expression except bit.

Return type

The return type depends on the base expression:

SQL Server raises an arithmetic overflow error if the result does not fit the return type. Convert the base to a wider type before calling POWER() when the result needs a larger range.

Examples

Here are two examples of the POWER() function:

Example 1

Calculate the second power of a number expression:

SELECT POWER(2, 2) AS Result;

Result:

Example 2

Use the POWER() function to calculate the interest on some compound interest:

SELECT 10000 * POWER(1 + 0.05, 5) AS Result;

Result:

Avoid integer overflow

Because an int base produces an int result, this expression overflows:

SELECT POWER(2, 31);

Convert the base to bigint before the operation to hold the result:

SELECT POWER(CONVERT(bigint, 2), 31) AS result;

The result is 2147483648. See SQL Server Error 8115: Arithmetic Overflow.

Conclusion

Use POWER() for exponentiation and choose a base type wide enough for the result. Its return type follows the base expression, not the exponent.