In SQL Server, the T-SQL POWER() function is a mathematical function that returns the value of the specified expression to the specified power.

The function accepts two arguments; the first specifies the expression, the second specifies the power with which to raise that expression.

The return value is the same data type as the first argument.

Syntax

The syntax goes like this:

POWER ( float_expression , y )

Where float_expression is an expression of type float or of a type that can be implicitly converted to float, and y is the power with 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.

Example 1 – Basic Usage

Here’s a basic example to demonstrate how this function works.

SELECT POWER(2, 3) Result;

Result:

+----------+
| Result   |
|----------|
| 8        |
+----------+

Example 2 – Negative Value

Here’s an example of passing in a negative value for the first argument.

SELECT 
  POWER(-2, 3) 'Result 1',
  POWER(-20, 3) 'Result 2',
  POWER(-2, 30) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| -8         | -8000      | 1073741824 |
+------------+------------+------------+

And here’s a negative value for the second argument:

SELECT 
  POWER(2, -3) 'Result 1',
  POWER(20, -20) 'Result 2',
  POWER(200, -300) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 0          | 0          | 0          |
+------------+------------+------------+

And next, both arguments are negative:

SELECT 
  POWER(-2, -3) 'Result 1',
  POWER(-20, -20) 'Result 2',
  POWER(-200, -300) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 0          | 0          | 0          |
+------------+------------+------------+

Example 3 – Zero

Here’s an example of passing in zero as the first argument.

SELECT 
  POWER(0, 3) 'Result 1',
  POWER(0, 20) 'Result 2',
  POWER(0, 300) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 0          | 0          | 0          |
+------------+------------+------------+

And zero for the second argument:

SELECT 
  POWER(3, 0) 'Result 1',
  POWER(20, 0) 'Result 2',
  POWER(300, 0) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 1          | 1          | 1          |
+------------+------------+------------+

Example 4 – Passing in 1

Passing in a value of 1 for the first argument.

SELECT 
  POWER(1, 3) 'Result 1',
  POWER(1, 30) 'Result 2',
  POWER(1, 300) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 1          | 1          | 1          |
+------------+------------+------------+

And 1 for the second argument:

SELECT 
  POWER(3, 1) 'Result 1',
  POWER(30, 1) 'Result 2',
  POWER(300, 1) 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 3          | 30         | 300        |
+------------+------------+------------+

Example 5 – Expressions

You can also pass in expressions like this:

SELECT POWER(5*2, 2) Result;

Result:

+----------+
| Result   |
|----------|
| 100      |
+----------+

That’s effectively the same as doing this:

SELECT POWER(10, 2) Result;

Result:

+----------+
| Result   |
|----------|
| 100      |
+----------+

LOG10()

Transact-SQL also has the LOG10() function which is inversely related to POWER().