SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with floating point precision
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sean Frost
Starting Member

USA
19 Posts

Posted - 12/04/2013 :  14:37:01  Show Profile  Reply with Quote
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 SP1


DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
GO

DECLARE @x FLOAT(24) = 1.0004;
DECLARE @y FLOAT(24) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33226895234962E-05
GO


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/calculator
Answer: 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-05

SELECT POWER((1+@z),12);                                 -- 1.00039999839968

-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12)                -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT 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.

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 12/04/2013 :  14:49:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why do you need float for non-scientific data? Wouldn't decimal work for this?

Float should very rarely be used.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Sean Frost
Starting Member

USA
19 Posts

Posted - 12/04/2013 :  14:59:33  Show Profile  Reply with Quote
Unfortunately no, Tara...

While I personally don't have that kind of money, my employer uses the 90-day T-bill as the benchmark for their multi-billion dollar investments. Ten digit precision works well, but the four digit precision SQL Server causes errors of hundreds of thousands of dollars in my calculations.

I don't necessarily have to use floating point data type. I tried using decimal(38,xx), but that didn't give me any good results either.

Edited by - Sean Frost on 12/04/2013 15:14:57
Go to Top of Page

Sean Frost
Starting Member

USA
19 Posts

Posted - 12/04/2013 :  15:22:51  Show Profile  Reply with Quote
I am getting a little closer with your suggestion, but still not quite there yet. I can't figure out what I am doing wrong if anything. I am asking for 30 digit scale (and it works only if I add all those trailing zeros).
DECLARE @x DECIMAL(38,30) = 1.00040000000000;
DECLARE @y DECIMAL(38,30) = 1.00000000000000/12.00000000000000;
SELECT POWER(@x,@y)-1.00000000000000; -- Answer: 0.000033327223783600000000000000
SELECT POWER (POWER(@x,@y),12);       -- Answer: 1.000400000000000800000000000000 -- close, but no cigar yet.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 12/04/2013 :  15:43:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
I think the issue is with the POWER function, and you are out of luck using it. See the last post and follow the link too: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45780

Instead, you could create a CLR function to do the calculations that you need. Call that function in your SQL code instead. CLR can use C#.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 12/04/2013 :  15:45:46  Show Profile  Reply with Quote
It might have to do with the Scale/precision adjustments that SQL makes behind the scenes:
http://msdn.microsoft.com/en-us/library/ms190476.aspx

Also if you explicitly cast your values, I think you'll get a more accurate answer. For example:
DECLARE @x DECIMAL(38,30) = CAST(1.00040000000000 AS DECIMAL(38,30));
DECLARE @y DECIMAL(38,30) = CAST(1.00000000000000 AS DECIMAL(38,30)) / CAST(12.00000000000000 AS DECIMAL(38,30)) ;
SELECT POWER(@x,@y)- CAST(1.00000000000000 AS DECIMAL(38,30)); 
SELECT POWER (POWER(@x,@y),12);
Go to Top of Page

Sean Frost
Starting Member

USA
19 Posts

Posted - 12/04/2013 :  16:35:00  Show Profile  Reply with Quote
Thank you Tara - that is an option I had not considered.

Temporarily, I interjected a tiny C# program into my process chain to do the calculations, which seems to work. Not as clean a solution as a CLR function would have been, but I am not very familiar with the mechanics of creating and installing a CLR function, so until I acquire more skills I have to limp along.
Go to Top of Page

Sean Frost
Starting Member

USA
19 Posts

Posted - 12/04/2013 :  16:40:09  Show Profile  Reply with Quote
Lamprey, none of the combinations seem to get me as close as C# or windows calculator. CLR seems attractive to me - because it enables me to integrate it into my other SQL code. So for the moment, that is my long term strategy.

Thank you for the link and for the suggestion. Regards.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000