Tutorial Gateway

SQL POWER Function

by

The SQL POWER Function is one of the Mathematical Function which used to calculate the power for the specified expression or numerical value.

SQL POWER Function Syntax

The basic syntax of the POWER Function in SQL Server is as shown below:

SELECT POWER (Float_Expression, Value)
FROM [Source]

In this article we will show you, How to write POWER Function in SQL Server with example. For this, We are going to use the below shown data

SQL POWER FUNCTION

SQL POWER Function Example 1

The POWER Function in SQL is used to calculate the power of a given numeric value. The following query will show multiple ways to use POWER function.

T-SQL CODE

DECLARE @i float = 2, @j int = 2.20

SELECT POWER(@i, 3) AS [SQL Powers]

SELECT POWER(@i, 4) AS [SQL Powers]

-- Calculating Power directly
SELECT POWER(3.20, 3) AS [SQL Powers]

-- Wrong Value
SELECT POWER(@j, 3) AS [SQL Powers]
SELECT POWER(2.20, 3) AS [SQL Power]

OUTPUT

SQL POWER FUNCTION 1

Analysis

Below lines of code is used to declare float and int variable and assigning the values.

DECLARE @i float = 2, @j int = 2.20

In the below statement, We used POWER function to calculate the power of the variable @i. We also assigned new name to that result as ‘SQL Power’ using SQL ALIAS Column.

SELECT POWER(@i, 3) AS [SQL Powers]

It means,

@i * @i * @i = 2 * 2 * 2 = 8

In the next statement, we used 4 as second argument. It means 2 * 2 * 2 * 2 = 16

SELECT POWER(@i, 4) AS [SQL Powers]

In the next statement, We used the integer value as input for the POWER function.

-- Wrong Value
SELECT POWER(@j, 3) AS [SQL Powers]
SELECT POWER(2.20, 3) AS [SQL Powers]

It means

POWER (@j, 3) = @j * @j * @j

= 2.20 * 2.20 * 2.20 = 10.65

But we are getting 8 as a result because it is rounding the 2.20 value to 2

NOTE: Please use float variables as a input for POWER Function otherwise, you may expect strange results.

SQL POWER Function Example 2

The Sql Server Power Function also allows you to calculate the power of column values.

In this example, We are going to calculate the power of three for all the records present in [Sales Amount] using POWER Function.

T-SQL CODE

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost]
      ,[SalesAmount]
      ,POWER([SalesAmount], 3) AS Sales
 FROM [Mathemetical Functions]

OUTPUT

SQL POWER FUNCTION 2

Thank You for Visiting Our Blog

Placed Under: SQL