Power function in T SQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

The scalar value .9 is a DECIMAL with 1 digit of precision and the same precision is returned. If you use at least 2 digits of precision you'll get what you're looking for:

SELECT POWER(.90, 2);

DECLARE @num DECIMAL(18,2) = .9;
SELECT POWER(@num, 2);

CodePudding user response：

Your passed value will have a default scale and precision of 1.

You can verify this by using

select precision, scale
from sys.dm_exec_describe_first_result_set(N'select .9 as test',null,0)