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

    Question

  •  

    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.

    Monday, September 25, 2006 5:23 PM

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.

    Monday, September 25, 2006 8:12 PM

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

    Monday, September 25, 2006 6:42 PM
  • 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.

    Monday, September 25, 2006 7:13 PM
  • 
    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.

    Monday, September 25, 2006 8:12 PM
  • 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.

    Monday, September 25, 2006 8:12 PM
  • 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 - )  

    Monday, September 25, 2006 8:51 PM
  • what is the exact difference between double and decimal data type? with example.

    Thursday, September 27, 2007 8:32 PM
  •  

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

    Friday, September 28, 2007 12:39 AM
  • 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)?

    Friday, September 28, 2007 5:29 PM