Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

POWER (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the value of the specified expression to the specified power.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

POWER ( float_expression , y )

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.

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).

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)

Community Additions

ADD
Show:
© 2015 Microsoft