POWER (Transact-SQL)
Returns the value of the specified expression to the specified power.
.gif "Topic link icon")
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
Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).
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)
See Also
Reference
decimal and numeric (Transact-SQL)
int, bigint, smallint, and tinyint (Transact-SQL)