What DataType is best ... float, double or decimal?

SQL Server Database Engine

FergusLogic


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.




Top

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

MVP User


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 floating-point arithmetic.
 

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
 
 

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.



Top

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

FergusLogic

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.






Top

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

MVP User

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

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.


Top

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

rottengeek

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.





Top

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

FergusLogic

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





Top

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

Zianj

what is the exact difference between double and decimal data type with example.





Top

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

apdicaprio

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


Top

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

Kent Waldrop Se07

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 aFloat

select datalength(cast(239820398423 as double precision)) as sizeOfDouble
select datalength(cast(239820398423 as float)) as sizeOfFloat

/*

aDoublePrecision
-----------------------------------------------------
2.3982033333339843E+17

aFloat
-----------------------------------------------------
2.3982033333339843E+17

sizeOfDouble
------------
8

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




Top