Power function in T SQL

The following returns .81 as expected:

select .9*.9;  

But this does not. Its return value is .8:

select power(.9, 2)

To have it return .81, I have to cast the input:

select power(cast(.9 as float), 2)

Why is a cast needed here? What am I missing?

Answer

The power function return type is dictated by the data type of the first parameter. Here, you’re giving it a decimal(1,1). As noted in the documentation, it’s returning a decimal(38,1).

You’ll get the result you want by either casting, as you’ve done, or by feeding the function differently.