SQL Server POWER() Function

Summary: in this tutorial, you will learn how to use the SQL Server POWER() function to raise a number to a specific power.

Introduction to SQL Server POWER() function

The POWER() function is a powerful math function that allows you to raise a number to a specific power.

The following shows the syntax of the POWER() function:

POWER(base, exponent)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• base: Specify the base number with the type float or a type that can be implicitly converted to float.
• exponent: Specify the exponent to which to raise the base number.

The POWER() function returns a number representing the result of raising the base number to the exponent.

The return type of the POWER() function depends on the type of the base number.

The following table shows the base number and the corresponding type of the return value:

If either base or exponent is NULL, the POWER() function returns NULL.

SQL Server POWER() function examples

Let’s take some examples of using the POWER() function.

1) Basic POWER() function examples

The following statement uses the POWER() function to raise the number 3 to the power of 4:

SELECT POWER(3,4) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
81Code language: SQL (Structured Query Language) (sql)

Similarly, you can use the POWER() function with decimal values:

SELECT POWER(1.5, 2) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
2.3Code language: SQL (Structured Query Language) (sql)

2) Using the POWER() function with a fractional exponent

The following statement uses the POWER() function to raise the number 2.5 to the power of 1.5:

SELECT POWER(2.0, 1.5) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
2.8Code language: SQL (Structured Query Language) (sql)

The return value is approximate, but not accurate.

If you use a base number as an integer, you’ll get the result as an integer. For example:

SELECT POWER(2, 1.5) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
2

3) Using the POWER() function with variables

The following example uses use the POWER() function to calculate compound interest:

-- Initial investment
DECLARE @Principal MONEY = 1000;

-- Annual interest rate (5%)
DECLARE @AnnualInterestRate FLOAT = 0.05;

-- Compounded yearly
DECLARE @CompoundingPeriodsPerYear INT = 1;

-- Years of investment
DECLARE @Years INT = 10;

-- Compound interest
DECLARE @CompoundInterest MONEY;

-- Calculate compound interest
SET
  @CompoundInterest = @Principal * POWER(
    (
      1 + (@AnnualInterestRate / @CompoundingPeriodsPerYear)
    ),
    (@CompoundingPeriodsPerYear * @Years)
  );

PRINT 'Compound Interest: ' + CONVERT(VARCHAR(20), @CompoundInterest);Code language: SQL (Structured Query Language) (sql)

Output:

Compound Interest: 1628.89Code language: CSS (css)

How it works.

First, declare variables that store the initial investment, annual interest rate, compound period per year, years of investment, and compound interest:

-- Initial investment
DECLARE @Principal MONEY = 1000;

-- Annual interest rate (5%)
DECLARE @AnnualInterestRate FLOAT = 0.05;

-- Compounded yearly
DECLARE @CompoundingPeriodsPerYear INT = 1;

-- Years of investment
DECLARE @Years INT = 10;

-- Compound interest
DECLARE @CompoundInterest MONEY;Code language: SQL (Structured Query Language) (sql)

Second, use the POWER() function to calculate compound interest:

-- Calculate compound interest
SET
  @CompoundInterest = @Principal * POWER(
    (
      1 + (@AnnualInterestRate / @CompoundingPeriodsPerYear)
    ),
    (@CompoundingPeriodsPerYear * @Years)
  );Code language: SQL (Structured Query Language) (sql)

Third, display the compound interest:

PRINT 'Compound Interest: ' + CONVERT(VARCHAR(20), @CompoundInterest);Code language: SQL (Structured Query Language) (sql)

Summary

• Use the POWER() function to raise a number to a specific power.