Float vs Decimal?

  • Ben Leighton

    SSCommitted

    Points: 1838

    Does anyone have any opinions concerning the performance of Float vs decimal data types?

    For example I have a table that stores balances, turnovers, and currency rates all of which were originally set up a float data types.

    The types of operations I am performing on the data are arithmetic... (no comparisons that would require converting float values to numeric etc) mainly calculating currency balances... Because the table is small, (only about 100k records long & I would expect it to grow at a rate of about 10% annually) I'm guessing that converting to Decimal data types might not improve performance that much, if at all.

    Does anyone have any thoughts about how significant an improvement I'd get?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Performancewise, I wouldn't think much, if any. Accuracy wise however... Float's an inaccurate data type and prone to rounding errors. It's not something I'd like to store financial values in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Michael Earl-395764

    SSC Guru

    Points: 53873

    My thoughts exactly - for performance it will probably be impossible to distinguish the two. However, you should not use FLOAT for financial values unless you are directing rounding errors into your retirement fund.

  • Jeff Moden

    SSC Guru

    Points: 993628

    Heh... Decimal is prone to more "rounding" errors than Float ever will be... it's a matter of rounding to the correct scale... For example, what should the answer to 1/3*3 be? Depending on the scale, it'll be some collection of 9's after the decimal point... With Float, it will more correctly be "1".

    I know... extraordinary claims require extraordinary proof...

    DECLARE @a DECIMAL(3,0), @B DECIMAL(18,0),@F FLOAT

    SET @a = 3

    SET @B = 3

    SET @F = 3

    SELECT 1/@A*3.0, 1/@B*3.0,1/@F*3.0

    SELECT 1/@A*3 , 1/@B*3 ,1/@F*3

    It's why the Money data type has 4 decimal places instead of 2... you should do all calculations with a higher scale than what you will display for the result. Your calculator works much the same way... if it displays 13 digits, it's doing it's calculations with 15 or more digits behind the scenes.

    Now, which is faster... assuming that you want some accuracy to the nearest penny, let's use Money, DECIMAL(x,4), and FLOAT...

    Here's the test data...

    DROP TABLE JBMTest

    GO

    --===== Create and populate a 1,000,000 row test table.

    -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDecimal has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDecimal has a range of 0.0000 to 99.9999_ non-unique numbers

    --Jeff Moden

    SELECT TOP 1000000

    SomeID = IDENTITY(INT,1,1),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDecimal = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS DECIMAL(19,4)),

    SomeFloat = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS FLOAT)

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

    --===== A table is not properly formed unless a Primary Key has been assigned

    -- Takes about 1 second to execute.

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (SomeID)

    ... and some code to test with...

    SET STATISTICS TIME ON

    UPDATE dbo.JBMTest

    SET SomeMoney = SomeMoney + 1.0

    UPDATE dbo.JBMTest

    SET SomeDecimal = SomeDecimal + 1.0

    UPDATE dbo.JBMTest

    SET SomeFloat = SomeFloat + 1.0

    SET STATISTICS TIME OFF

    When writing to disk... decimal wins every time... but at the rate of about 300 milliseconds spread over a million rows. So, I say, who cares? Use what works best for you.

    When doing memory calculations...

    DECLARE @BitBucketM MONEY

    DECLARE @BitBucketD DECIMAL(19,4)

    DECLARE @BitBucketF FLOAT

    SET STATISTICS TIME ON

    SELECT @BitBucketM = SomeMoney + 1.0

    FROM dbo.JBMTest

    SELECT @BitBucketD = SomeDecimal + 1.0

    FROM dbo.JBMTest

    SELECT @BitBucketF = SomeFloat + 1.0

    FROM dbo.JBMTest

    SET STATISTICS TIME OFF

    ... Float wins but not by much over decimal. Consider again how small the difference is over a million rows.

    Again, my recommendation is that you make money calculations out to at least 4 decimal places and do the correct rounding if needed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993628

    And... you might just want to learn to use Float for financial calculations and then round the answer to make the humans happy. A great many of the functions in SQL Server use Float as a returned datatype, so consider that you may be using Float even if you don't think so. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Ben Leighton

    SSCommitted

    Points: 1838

    I kind of agree with what you are saying about decimal vs float for financial data... Actual financial data is stored seperately using the appropriate datatype. This table is used for calculating currency revaluations over a given period.

    but what you say kind of raises an issue... (an exception that proves the rule.), and a better explanation of why I'm asking the question in the 1st place.

    this table contains inputted data and calculated data... So for example I'm right with you that inputted turnovers etc should normally be stored as decimals... it is an exact record of what has been inputted after all. Now the problem starts to occur when we start calculating currency values...

    If I am recording an exchange rate it makes sense to store it to 6dp. A decimal datatype looks an obvious choice. Now for business reasons I need to calculate an inverse rate as well...

    So if I convert my $10,000,000,000 to sterling using a direct rate and convert back using my inverse rate (as a decimal) I could get massive differences... if my inverse rate is stored to the same precision as the original rate.

    The following code illustrates my point

    declare

    @dec as decimal(18,6),

    @res as decimal(18,6),

    @flo as float,

    @dec2 as decimal(18,6)

    set @dec = 1.909091

    set @flo = 1/convert(FLOAT,@DEC)

    set @res = 1/@dec

    print @dec

    print @flo

    PRINT @RES

    set @dec2 = 100000000 * @dec

    print @dec2

    set @dec2= 100000000/@RES

    print @dec2

    set @DEC2 = 100000000/@FLO

    print @DEC2

    Which is why the original data is stored as a float...

    But I'd still feel more comfortable storing my data as a decimal (possibly giving my inverse rate a greater precision).

    However if there's no trade of from improved performance using decimal then the cost of converting to a decimal of greater precision might prove significant. Any thoughts?

  • Ben Leighton

    SSCommitted

    Points: 1838

    sorry looks to me that Jeff has just made the point I'm trying to make. Thanks to all!

  • noeld

    SSC Guru

    Points: 96590

    You may want to "see" more here

    By the way I work with financial data and we use "floats" 😉


    * Noel

  • Jeff Moden

    SSC Guru

    Points: 993628

    Ben Leighton (6/24/2008)


    sorry looks to me that Jeff has just made the point I'm trying to make. Thanks to all!

    Absolutely not a problem, Ben. Thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • matt stockham

    SSCrazy Eights

    Points: 9892

    Rather than a question of decimal vs float, it's a question of scale and precision .... just ensure that the datatype you choose is sufficient for your purposes. I typically use money for stored values, decimals for calculations and interest/FX rates.

  • umailedit

    SSCrazy

    Points: 2087

    I prefer fixed decimal for storing values because aggregating them produce perfect results. But while calculating (especially division), I cast to float, do the calc and convert back to fixed to store. Fixed is very important (for storage) in a double entry accounting system otherwise the entries will not balance. But float is very important for doing intermediate calculations because there is no fixed point processor (esp for division) but all cpus have floating point processors.

  • jezemine

    SSCrazy Eights

    Points: 8029

    also float is really fun to use for surrogate keys.

    😉

    ---------------------------------------
    elsasoft.org

  • Ross McMicken

    SSCarpal Tunnel

    Points: 4372

    On more than one occasion, I've wished for a BCD data type like IBM uses on the AS/400 (iSeries now). Specify enough places, nad there's never a rounding issue. Of course, it works for IBM boxes because they put all the arithmetic operations in hardware to reduce the performance penalty associated with BCD.

  • Mauve

    SSChampion

    Points: 11256

    Having worked on a financial system in the early 90's where we were computing gain/loss per share per day it got quite interesting with the precision factor. In particular with some currencies.

    What happened was that the "sum of the parts" (daily gain/loss) didn't equal the "whole" (net result) after a certain amount of time. The system was designed with a pre-specified number of decimal places using a DECIMAL datatype.

    Fortunately, the underlying RDBMS was Oracle where we could just remove the scale factor by redefining the column's datatype to just an unqualified [Oracle's] NUMBER. This equated to DECIMAL(38,*). So it would store up to 38 digits in total with a "floating" number of decimal places. Analysis of the resulting gain/loss values showed that we needed at least 22 decimal places for some currencies.

    For financial systems, accuracy is paramount.

    So I'd use DECIMAL and enable the new VARDECIMAL option if space will be an issue.

    See my related post:

    http://www.sqlservercentral.com/Forums/Topic437894-1042-5.aspx#bm440631


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • Matt Miller (4)

    SSC Guru

    Points: 124136

    ....you KNOW you're supposed to do the multiplies FIRST, and then the divides... Regardless of the storage mechanism you're using, you're aritificially aggravating the amount of error.

    Makes my blood run cold everytime I see that. Probably has something to do with my 3rd grade science teacher and that #$%^&$%# ruler. My knuckles still hurt when I think of that.....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 15 posts - 1 through 15 (of 31 total)

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