POWER (Transact-SQL)
SQL Server 2012
Returns the value of the specified expression to the specified power.
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)
