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:
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;
Code language: SQL (Structured Query Language) (sql)
Output:
result
------
81
Code 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.3
Code 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.8
Code 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.89
Code 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.