SQL POWER FUNCTION 1

SQL POWER Function

by

The SQL POWER Function is a SQL Mathematical Function that is used to calculate the power for the specified expression or numerical value. The syntax of the SQL Server POWER Function is

SELECT POWER (Float_Expression, Value)
FROM [Source]

SQL POWER Function Example 1

The SQL POWER Function calculates the power of a given numeric value. The following query shows multiple ways to use the POWER function.

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]
SQL POWER FUNCTION 1

We used the POWER function to calculate the power of the variable @i.

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

It means, @i * @i * @i = 2 * 2 * 2 = 8. In the next SQL Server statement, we used 4 as second argument. It means 2 * 2 * 2 * 2 = 16

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

Here 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: Use float variables as the POWER Mathematical Function inputs. Otherwise, you may expect strange results.

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.

For this POWER Function demo, we use the Math Table data

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