loading..
Ðóññêèé    English
22:45

POWER and SQRT functions

The SQL Server POWER (x, y) function raises x to the power of y.

x is an expression of type FLOAT or of a type that can be implicitly converted to FLOAT.

y is an expression of a numeric type.

The returned result has the same type as x.

The SQRT (x) function calculates the square root of x, x being an expression of type FLOAT or of a type implicitly converted to it. The returned result is of FLOAT type.

SQRT is the inverse function of POWER(x, 2), so that SQRT(POWER(x,2)) should return x.

Let’s check this

Console
Execute
  1. SELECT 3 x, power(3,2) y, sqrt(power(3,2)) sqrt_y;

x    y    sqrt_y
3    9    3

This is correct. However,

Console
Execute
  1. SELECT 3.1 x, power(3.1,2) y, sqrt(power(3.1,2)) sqrt_y;
yields

x    y    sqrt_y
3.1    9.6    3.09838667696593

This unexpected result in most likelihood has to do with a loss of precision when the POWER function result type (which matches the type of the argument, NUMERIC) is implicitly converted to FLOAT.

Indeed,

Console
Execute
  1. SELECT SQL_VARIANT_PROPERTY(3.1,'BASETYPE') basetype

basetype
numeric

When we apply an equivalent conversion to retain the NUMERIC type for the result returned

Console
Execute
  1. SELECT 3.1 x, power(3.1,2) y, power(power(3.1,2),0.5) sqrt_y;
everything works as expected.

x    y    sqrt_y
3.1    9.6    3.1

We will get the same result by converting the argument of POWER to FLOAT in the example with SQRT usage. In this case, POWER returns a value of type FLOAT, so that no further conversion is needed:

Console
Execute
  1. SELECT 3.1 x, power(3.1,2) y, sqrt(power(CAST(3.1 AS float),2)) sqrt_y;

x    y    sqrt_y
3.1    9.6    3,1

And if we reverse the order the functions are applied, no undesired "miracles" occur:

Console
Execute
  1. SELECT power(sqrt(9.6),2) power_;

power_
9,6

In this example, SQRT returns a value of type FLOAT, eliminating the need for any conversions.

Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
today
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.