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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 FLOAT datatype
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aprichard
Yak Posting Veteran

India
62 Posts

Posted - 04/16/2009 :  09:16:00  Show Profile  Reply with Quote

Can any one of you explain drawback of float type.

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/16/2009 :  09:33:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It's inprecise for decimals. Integer part will work ok.
It uses a binary storing algorithm due to the IEEE-754 standard it uses.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/16/2009 :  09:45:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The reason is that the decimal part must be represented in the way

(bit1 / 2^1) + (bit2 / 2^2) + (bit3 / 2^3) + (bit4 / 2^4) + ...
(bit1 / 2) + (bit2 / 4) + (bit3 / 8) + (bit4 / 16) + ...

If you sum all these combinations to infinity the sum is exactly 1, which means in theory all possible decimal values can be used and represented.
However IEEE754 says only 52 bits can be used for decimals. So smallest value is 1 / 2^52 (roughly 2.22044604925031E-16)
and all possible values to store are multiples of this smallest possible value.


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 04/16/2009 09:51:29
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/16/2009 :  10:50:57  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
[everything Peso said] = rounding errors!

compare FLOAT and DECIMAL / NUMERIC in books on line


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 06/28/2013 :  04:45:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And what is also important, if you restore your database containing a FLOAT, the value might differ if the new server has another kind of processor.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

michael.appleton
Posting Yak Master

United Kingdom
160 Posts

Posted - 06/28/2013 :  05:21:59  Show Profile  Click to see michael.appleton's MSN Messenger address  Reply with Quote
I think an easy to imagine a floating point number is usually a very good approximation of a number. It you need something to be exact, don't use a floating point number.

Edited by - michael.appleton on 06/28/2013 05:22:53
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.12 seconds. Powered By: Snitz Forums 2000