Answered by:
What DataType is best ... float, double or decimal?

I am using sql express 2005 and sql server 2005 with C# 2.0.
I am a bit confused about which data type i should be using for several fields.
Right now I am declaring all of my fields in sql server as float for everything except for money fields which are using money.
When loaded into C# these fields are converted to double and decimal because C# does not have a float datatype.
Should I be using Decimal or Double for everything instead?
Here are a few examples
QtyInvoiced (float)  holds the number of items invoice
possible values look like this 1.0, 1.25 or 1.5
PercentDiscount (float)  holds a percentage
possible values look like this
10.25, 20.50, 50.00
I appreciate the help.
Question
Answers

I'm not sure about 2005, but MS says that in 2000 'float' and 'real' are approximations. They aren't going to be exact, and when converting these values to other datatypes you can get 'unpredictable' results. I've seen this happen  and it can really do squirrelly things.
I have avoided these data types, especially for financial applications, as there is so much front end user reporting using different applications (sanctioned or not) that results can get very skewed.
I have even gotten differing results for the same computation  i avoid float like the plague.
From BOL 2000:
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.
All replies

You can have 1.5 items in invoice? What is the business? I've never heard of a store selling me 1.5 of anything ;)I would use personally DECIMAL for everything, including the money (which is really nothing more than DECIMAL under the covers with some special string formatting capabilities), unless you have a specific need for floatingpoint arithmetic.

Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
<FergusLogic@discussions.microsoft.com> wrote in message news:36ba59955e384b2cbfeab34ca7b60560@discussions.microsoft.com...I am using sql express 2005 and sql server 2005 with C# 2.0.
I am a bit confused about which data type i should be using for several fields.
Right now I am declaring all of my fields in sql server as float for everything except for money fields which are using money.
When loaded into C# these fields are converted to double and decimal because C# does not have a float datatype.
Should I be using Decimal or Double for everything instead?
Here are a few examples
QtyInvoiced (float)  holds the number of items invoice
possible values look like this 1.0, 1.25 or 1.5
PercentDiscount (float)  holds a percentage
possible values look like this
10.25, 20.50, 50.00
I appreciate the help.


Yes, I've been to many repair shops  and it seems that they ALWAYS round up to the nearest whole integer ;)

Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
<FergusLogic@discussions.microsoft.com> wrote in message news:5515d7d0477a41d4aae30c08ac9a81d4@discussions.microsoft.com...Sure..ever been to a repair shop?
1.25 hours of labor
4.5 quarts of oil.
:)
thanks for the help. I will adjust all the floats to decimal i guess.

I'm not sure about 2005, but MS says that in 2000 'float' and 'real' are approximations. They aren't going to be exact, and when converting these values to other datatypes you can get 'unpredictable' results. I've seen this happen  and it can really do squirrelly things.
I have avoided these data types, especially for financial applications, as there is so much front end user reporting using different applications (sanctioned or not) that results can get very skewed.
I have even gotten differing results for the same computation  i avoid float like the plague.
From BOL 2000:
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

Thank you everyone. I appreciate all of the great advice (and shop humor) .
I will modify my floats to decimal. Luckily I thought that this problem might arise so i wrote down every table and stored procedure that is currently using float so i can make the change quickly.
With that said...it is still a huge pain in the butt..but thats how the ball rolls i guess. 8  )


A float is represented as powers of two which cause problems.
1.2 is
1 * 2^0 + 0 * 2^1 + 0*2^2 + 1*2^3 + 1*2^4 + 0*2^5 + 0*2^6 + 1*2^7
1 + .125 + .0625 + .007815 = 1.195315 (floating point will go a bit further down but you get the idea)
There is actually an excel bug reported recently directly reguarding floating point issues.
decimal on the other hand:
1.2 is 1.2. There is no rounding
You will need more bits to represent
123456789.123456789123456789 with a decimal as compared to a float but the decimal value is exact.
The catch is that it takes more space to be able to represent the same number a float may show. Floats may be good for science, but every day life, use decimal because otherwise you will get a number that isn't what you expect (yes, maybe too generalized but I have seen too many times where money is stored in a float field which has some fun implications when trying to actually tie out everything).

As to the issue of "double" or "float" they are the usually the same in SQL Server; however, DOUBLE is virtually never used. Therefore, use FLOAT instead of the "DOUBLE PRECISION" data type. For example:
select cast(239820333333398423 as double precision) as aDoublePrecision
select cast(239820333333398423 as float) as aFloatselect datalength(cast(239820398423 as double precision)) as sizeOfDouble
select datalength(cast(239820398423 as float)) as sizeOfFloat/*
aDoublePrecision

2.3982033333339843E+17aFloat

2.3982033333339843E+17sizeOfDouble

8sizeOfFloat

8
*/Question: Is the "DOUBLE PRECISION" spec tagged for deprecation? It is tough to find any reference to this any more. Or is it that "DOUBLE PRECISION" refers strictly to FLOAT(53)?