I found out an interesting thing about the SQL Server POWER function. I needed a historical average for a pH value which requires using the POWER function. In testing we noticed bizarre behavior:
SELECT POWER(10.0, -7.0) -- Returns: 0.0
GO
When you read the documentation, there is an example where SELECT POWER(2.0, -100.0) returns 0.0 and the documentation claims it is due to floating point underflow. What the documentation neglects to tell you is the conversion of literal numbers -- it must be converting the literal number into a DECIMAL type instead of a FLOAT or REAL data type as most C/C++/C#/Java/etc. programmers would assume. Here are some examples
SELECT POWER(10.0, -7.0) -- Returns: 0.0
SELECT POWER(10.0000000, -7.0) -- Returns: 0.0000001
GO
DECLARE @a float, @b float
SELECT @a = 10.0, @b = -7.0
SELECT POWER(@a, @b) -- Returns: 1E-07
SELECT @a = 2.0, @b = -100.0
SELECT POWER(@a, @b) -- Returns: 7.88860905221012E-31
GO
So be careful when using system functions that accept multiple data types and don't assume a floating point literal is actually converted into FLOAT or REAL as it may be DECIMAL instead.