I'm having trouble using the POWER() function. Two integer columns hold a value and an exponent respectively. When I write a query that uses a negative exponent, the query returns 0. For example, the following query (which for simplification uses only constants):

SELECT 2 * CAST(POWER(10, -2) AS DECIMAL(9,3))

returns .000. I expected the query to return .020. Why didn't the query return the expected result?

To answer your question, let's first look at an excerpt from SQL Server *Books Onlin*e*'s (BOL)* description of the way the POWER() and EXP() exponential functions work:

*"The POWER function returns the value of the given numeric expression to the specified power. POWER(2,3) returns 2 to the third power, or the value 8. Negative powers can be specified, so POWER(2.000, -3) returns 0.125. Notice that the result of POWER(2, -3) is 0. This is because the result is the same data type as the given numeric expression. Therefore, if the result has three decimal places, the number to raise to a given power must have three decimals, too."*

Applying that explanation to your problem, you need to convert the POWER function's first argument to a decimal data type. To perform the conversion, you can choose either of the following alternatives:

SELECT 2 * POWER(CAST (10 AS DECIMAL(9, 3)), -2)

or

SELECT 2 * POWER(10 * 1.000, -2)

That way, you apply the POWER() function to a decimal so that you can return a decimal.