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

select 3 x, power(3,2) y, sqrt(power(3,2)) sqrt_y;

This is correct. However,

select 3.1 x, power(3.1,2) y, sqrt(power(3.1,2)) sqrt_y;

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,

select SQL_VARIANT_PROPERTY(3.1,'BASETYPE') basetype

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

select 3.1 x, power(3.1,2) y, power(power(3.1,2),0.5) sqrt_y;

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:

select 3.1 x, power(3.1,2) y, sqrt(power(cast(3.1 as float),2)) sqrt_y;

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

select power(sqrt(9.6),2) power_;

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