float vs. decimal

Question

float vs. decimal

lof

Default port

Points: 1498
More actions
October 2, 2002 at 10:34 am

#79673

Hi,
We are writting a scientific application based on SQL2k. Number of fields in it will be numeric. Often numbers between various fields need to be compared. We have found out that when we store two identical numbers in the float data type fields, they will not compare as they are stored with various number of decimals (way too many often). So ve are considering use of decimal data type instead of float. Can someone give me any input one way or another on this issue?
We are not trying to land on Mars, but true representation of results is very important. We can't use money data types because our numbers can have more that four decimals (up to four significant figures).
any help is appreciated.
Thanks.
Jakub

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

There was a good article on this I read recently, can't remember the URL now. Basically since the float is an imprecise value, you are better off using decimals or numerics for scientific apps.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

cobra429 SSC Veteran Points: 204 More actions · Answer 2

Steve is correct. A decimal values is actually converted to its binary represenation prior to storing as a float. Therefore the value is only an estimated or approximate number. The real advantage to using Floats is that the precision for a float is up to 15 digits whereas it is only 8 digits for a real number. I hope this helps

lof Default port Points: 1498 More actions · Answer 3

Thanks for your answers. But, the way I read BOL, float has precision of 15 digits, while the decimal format can have up to 38 digits, at the price of storage obviously (8 bytes vs. 17 bytes). But even at the precision of 19 the decimal takes just one more byte of storage then float and it should represent the actual number stored in the field.

Or am I missing something?

How is the representation through various fron ends for the decimal? I know that EM represents different view of float than Query Analyzer. how about ADO, VB, etc..

Thanks,

Jakub

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

Interesting subject.

Search BOL for "Using decimal, float, and real Data".

Things start to go wrong when you multiply/divide a small number with a big value(exponent ,mantissa, loss of accuracy).

BOL = "Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value."

lof Default port Points: 1498 More actions · Answer 5

Yeah,

I saw it. that's why we are converting to the decimal. It will work fine for us at a small price in the size of the fields. But at least rounding and calculations will be correct.

Jakub