Do you use the float datatype?

I thought I’d perhaps blog about this although I don’t truly think this is of any real significance unless you like to use the flat datatype, which I hope you don’t.

 

I had restored a production backup onto a development box and the guys had run a couple of checks and found a sum of the data returned different values. As this is a financial system the fact there is a difference is important.

 

My initial thought was that there was something about a float datatype but I couldn’t remember exactly what, my vague memories were that the floating point precision of the cpu affected the accuracy – I should point out that I don’t recommend using the float datatype and always use numeric, however, this is a client database and an answer was required.

I posted and eventually received confirmation:-

 

http://www.sqlcommunity.com/Discussions/tabid/54/forumid/10/threadid/18/scope/posts/Default.aspx

A float is an approximately data type.  It will not produce the same results from one processor architecture to another and won't even necessarily do so from one machine to another.  In addition, you are using a float in computations, then you will be compounding rounding factors which magnifies the effect.  If you need the calculations to a high precision as well as also requiring them to be exactly the same regardless of machine or processor architecture, then you need to replace the float data type with a decimal data type.

Michael Hotek Vice-President MHS Enterprises, Inc President FilAm Software Technology, Inc.

 

Shawn also pointed me to Ken Henderson’s blog , thanks,

 

http://blogs.msdn.com/khen1234/archive/2005/05/13/417153.aspx

 

However I took Ken’s sample code and ran it across a few servers, on the SQL 2000 servers I got the same results as Ken but I could not get this with SQL 2005 32 or 64 bit so maybe the float isn’t so vague in SQL 2005.

 

Published 26 October 2007 10:09 by GrumpyOldDBA
Filed under: ,

Comments

# re: Do you use the float datatype?

26 October 2007 10:57 by GBN

Thanks for the mention.

float also only goes to 15 significant figures.

Cheers

# re: Do you use the float datatype?

26 October 2007 21:37 by Hugo Kornelis

Hi Grumpy,

May I ask why you hope that your readers don't use the float datatype? As I have recently written on my blog (see http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx), both "exact" and "appproximate" numerics have both strong AND weak points, and both types have their place.

I'm also quite intrigued by your statement that float calculations "will not produce the same results from one processor architecture to another and won't even necessarily do so from one machine to another". Do you have a pointer to any documentation or any repro to prove this? As far as I know, this is not true.

Best, Hugo

# re: Do you use the float datatype?

26 October 2007 22:06 by Alex_Kuznetsov

Hi Hugo,

consider the following example:

declare @big float, @small float, @sum1 float, @sum2 float, @sum3 float, @i INT

SELECT @big = 12345678901234.50, @small = 0.01, @i = 0

SELECT @sum1 = @big, @sum2 = 0, @sum3 = 0

WHILE @i < 5 BEGIN

 SELECT @sum1 = @sum1 + @small, @sum2 = @sum2 + @small, @sum3 = @sum3 + @small

 SET @i = @i + 1

END

SELECT @sum2 = @sum2 + @big

SELECT @sum1, @sum2, @sum3

---------------------- ---------------------- ----------------------

12345678901234.5       12345678901234.6       0.05

(1 row(s) affected)

# re: Do you use the float datatype?

26 October 2007 22:47 by Hugo Kornelis

Hi Alex,

Is this query supposed to serve as a repro to show that the results of float calculations can vary from machine to machine? If so, it doesn't fly, as I get the same results that you post on my SQL Server 2005 and my SQL Server 2000 instance.

Best, Hugo

# re: Do you use the float datatype?

27 October 2007 02:37 by Alex_Kuznetsov

Hugo,

note that although I added the same six numbers, @sum1 and @sum2 are different. My example is supposed to illustrate that if I have a table with a float column, the sum of that column may depend on the execution plan. If I rebuild my clustering index, rebuild statistics or just say MAXDOP=1, I can get slightly different totals from exactly the same data. Similarly, I restore a production backup on a single processor workstation, i also can get slightly different totals.

# re: Do you use the float datatype?

27 October 2007 02:38 by Alex_Kuznetsov

Hugo,

note that although I added the same six numbers, @sum1 and @sum2 are different. My example is supposed to illustrate that if I have a table with a float column, the sum of that column may depend on the execution plan. If I rebuild my clustering index, rebuild statistics or just say MAXDOP=1, I can get slightly different totals from exactly the same data. Similarly, I restore a production backup on a single processor workstation, i also can get slightly different totals.

# re: Do you use the float datatype?

27 October 2007 09:05 by Hugo Kornelis

Hi Alex,

I noted that the order of addition changes the results, and I already commented on that on your own blog. I was just wondering if your choice to duplicate the script here was related to the questions I asked Grumpy Old DBA in my first comment. Obviously, it wasn't.

Best, Hugo

# re: Do you use the float datatype?

27 October 2007 13:19 by GrumpyOldDBA

logically anything which is approximate is questionable and as the prompt for the original problem was in financial data being returned you have to ask yourself if you'd want to use something which is approximate in a financial system. The point in question for me was that the two servers were different cpu manufacturers. I was summing around 195k rows, which I can't show here, I had compared the data to make sure that all the values in the rows was identical, however the sum was not identical. Mike Hotek confirmed my view and in general terms I'd take that as being proof. Ken Henderson's example will produce a variance in the results most times you run this on any sql 2000 server, you might want to run it several times to illustrate the point.

I personally can't see any reason to use data which is approximate - but I do generally work in financials - try asking your finance director if approximate figures are acceptable < grin >

Thanks for the comments.

# re: Do you use the float datatype?

27 October 2007 15:51 by Hugo Kornelis

Hi Grumpy Old DBA,

When working with financial data, you will indeed always want to use numeric(xx,2) as your datatype. No discussion there.

However, if you ever find yourself working with scientific data, you'll probably learn to love float faster than you can say "number of significant figures"

Best, Hugo

# re: Do you use the float datatype?

27 October 2007 23:23 by simonsabin

I don't agree with you about the 2 decimal place point.

Whilst you may only report at 2 decimal places often calculations require more decimal places to be used.

The key aspect is that rounding needs to be stored somewhere. Otherwise you end up with a Gus Gorman situation http://en.wikipedia.org/wiki/Gus_Gorman

# Are floats inaccurate?

16 November 2007 00:18 by SimonS Blog on SQL Server Stuff

One often thinks that the use of decimals provides the most accurate recording of numbers with decimal