I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1DECLARE @x FLOAT(53) = 1.0004;DECLARE @y FLOAT(53) = 1.0/12.0;SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05GODECLARE @x FLOAT(24) = 1.0004;DECLARE @y FLOAT(24) = 1.0/12.0;SELECT POWER(@x,@y)-1; -- Answer: 3.33226895234962E-05GO
I did the same calculation using a C# code static void Main(string[] args) { double x = 1.0004; double y = 1.0 / 12; System.Console.WriteLine(Math.Pow(x, y)-1); }Answer: 3.33272237835747E-05
I also tried using Windows Calculator. Answer:3.3327223783495255846580902358195e-5
And an online "high-precision" calculator from http://keisan.casio.com/calculatorAnswer: 3.332722378349525584658E-5
The best SQL results compare only to the fourth digit with other results. Everything else agrees to the 10th or 12th digit. Is the precision of arithmetic calculations on SQL Server that bad, or am I doing something wrong here? Is there another more precise alternative?Just to humor myself, I did the following. The results speak for themselves DECLARE @x FLOAT(53) = 1.0004;DECLARE @y FLOAT(53) = 1.0/12.0;DECLARE @z FLOAT(53) = POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05SELECT POWER((1+@z),12); -- 1.00039999839968-- using results from other sources (c#, windows calc, casio.com)SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will help, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.