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)Copy

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:

Type of base number	Return type
float, real	float
decimal(p, s)	decimal(38, s)
int, smallint, tinyint	int
bigint	bigint
money, smallmoney	money
bit, char, nchar, varchar, nvarchar	float

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;Copy

Output:

result
------
81Copy

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

SELECT POWER(1.5, 2) result;Copy

Output:

result
------
2.3Copy

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;Copy

Output:

result
------
2.8Copy

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;Copy

Output:

result
------
2Copy

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);Copy

Output:

Compound Interest: 1628.89Copy

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;Copy

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

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

Third, display the compound interest:

PRINT 'Compound Interest: ' + CONVERT(VARCHAR(20), @CompoundInterest);Copy

Summary #

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

Was this tutorial helpful?

YesNo