power-transact-sql.md

description	POWER (Transact-SQL)
title	POWER (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/13/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, synapse-analytics, pdw
ms.reviewer
ms.technology	t-sql
ms.topic	reference
f1_keywords	POWER_TSQL POWER
dev_langs	TSQL
helpviewer_keywords	POWER function
ms.assetid	0fd34494-90b9-4559-8011-a8c1b9f40239
author	LitKnd
ms.author	kendralittle
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

POWER (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

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

Transact-SQL Syntax Conventions

Syntax

POWER ( float_expression , y )  

[!INCLUDEsql-server-tsql-previous-offline-documentation]

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;  

[!INCLUDEssResult]

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  

[!INCLUDEssResult]

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  

[!INCLUDEssResult]

-----------   
2             
  
(1 row(s) affected)  
  
-----------   
4             
  
(1 row(s) affected)  
  
-----------   
8             
  
(1 row(s) affected)  
  
-----------   
16            
  
(1 row(s) affected)  

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

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

[!INCLUDEssResult]

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