POWER (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Returns the value of the specified expression to the specified power.
Transact-SQL syntax conventions
Syntax
POWER ( float_expression , y )
Arguments
float_expression
Is an expression of type float or of a type that can be implicitly converted to float.
y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Return Types
The return type depends on the input type of float_expression:
Input type | 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 the result does not fit in the return type, an arithmetic overflow error occurs.
Examples
A. Using POWER to return the cube of a number
The following example demonstrates raising a number to the power of 3 (the cube of the number).
DECLARE @input1 FLOAT;
DECLARE @input2 FLOAT;
SET @input1= 2;
SET @input2 = 2.5;
SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2;
Here is the result set.
Result1 Result2
---------------------- ----------------------
8 15.625
(1 row(s) affected)
B. Using POWER to show results of data type conversion
The following example shows how the float_expression preserves the data type which can return unexpected results.
SELECT
POWER(CAST(2.0 AS FLOAT), -100.0) AS FloatResult,
POWER(2, -100.0) AS IntegerResult,
POWER(CAST(2.0 AS INT), -100.0) AS IntegerResult,
POWER(2.0, -100.0) AS Decimal1Result,
POWER(2.00, -100.0) AS Decimal2Result,
POWER(CAST(2.0 AS DECIMAL(5,2)), -100.0) AS Decimal2Result;
GO
Here is the result set.
FloatResult IntegerResult IntegerResult Decimal1Result Decimal2Result Decimal2Result
---------------------- ------------- ------------- -------------- -------------- --------------
7.88860905221012E-31 0 0 0.0 0.00 0.00
C. Using POWER
The following example returns POWER
results for 2
.
DECLARE @value INT, @counter INT;
SET @value = 2;
SET @counter = 1;
WHILE @counter < 5
BEGIN
SELECT POWER(@value, @counter)
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END;
GO
Here is the result set.
-----------
2
(1 row(s) affected)
-----------
4
(1 row(s) affected)
-----------
8
(1 row(s) affected)
-----------
16
(1 row(s) affected)
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
D: Using POWER to return the cube of a number
The following example shows returns POWER
results for 2.0
to the 3rd power.
SELECT POWER(2.0, 3);
Here is the result set.
------------
8.0
See Also
decimal and numeric (Transact-SQL)
float and real (Transact-SQL)
int, bigint, smallint, and tinyint (Transact-SQL)
Mathematical Functions (Transact-SQL)
money and smallmoney (Transact-SQL)