The POWER Function in SQL is Crazy

T-SQL’s POWER function baffled me. It rounds its result to the number of decimals that have been given in its first argument. I have not found any other function or math operation in SQL which does this.

It is absolutely astonishing and just nonsense. You need to be aware of it to avoid very bad miscalculations. See this:

SELECT 0.5 * 0.5, POWER(0.5, 2), POWER(0.5, 2.0000)  
  -->  0.25           0.3             0.3

This is not just a display effect of MS SQL Server Management Studio. It really calculates with these rounded values. See and marvel.

SELECT (0.5 * 0.5) * 4, POWER(0.5, 2) * 4 
 --> 1.00   1.2

Look further and wonder.

SELECT 0.5/1000, 0.5*0.001,  POWER(0.10, 3), 0.5 * POWER(0.100, 3)
 -->   0.000500    0.0005            0.000          0.0005

SELECT 1.0/10000, 1.0*0.0001, POWER(0.10, 4), POWER(0.1000, 4)
 -->   0.0001000    0.00010     0.00            0.00001