Float vs Decimal?

Question

Float vs Decimal?

Ben Leighton

SSCommitted

Points: 1838
More actions
June 24, 2008 at 3:26 am

#193099

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?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

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: 1003857 More actions · Answer 3

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-2 DECIMAL(18,0),@F FLOAT

SET @a = 3

SET @b-2 = 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben Leighton SSCommitted Points: 1838 More actions · Answer 5

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 More actions · Answer 6

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

noeld SSC Guru Points: 96590 More actions · Answer 7

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: 1003857 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 9

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 More actions · Answer 10

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: 8059 More actions · Answer 11

also float is really fun to use for surrogate keys.

😉

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

Ross McMicken SSCarpal Tunnel Points: 4395 More actions · Answer 12

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: 11316 More actions · Answer 13

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: 124210 More actions · Answer 14

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