 |
|||||
go
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
This is correct. However, yields
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,
When we apply an equivalent conversion to retain the NUMERIC type for the result returned everything works as expected.
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
And if we reverse the order the functions are applied, no undesired "miracles" occur:
In this example, SQRT returns a value of type FLOAT, eliminating the need for any conversions. |
|||||||||||||||||||||||||||||||||||||
