Help with floating point precision

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with floating point precision

Author

Topic

Sean Frost
Starting Member

19 Posts

Posted - 2013-12-04 : 14:37:01

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

38200 Posts

Posted - 2013-12-04 : 14:49:39

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/

Sean Frost
Starting Member

19 Posts

Posted - 2013-12-04 : 14:59:33

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.

Sean Frost
Starting Member

19 Posts

Posted - 2013-12-04 : 15:22:51

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-04 : 15:43:07

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/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-04 : 15:45:46

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);

Sean Frost
Starting Member

19 Posts

Posted - 2013-12-04 : 16:35:00

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.

Sean Frost
Starting Member

19 Posts

Posted - 2013-12-04 : 16:40:09

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources