Float vs Decimal?

Question

Post reply

Float vs Decimal?

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 1

JohnG (6/26/2008)
For financial systems, accuracy is paramount.
So I'd use DECIMAL

And, that is precisely why I'd use float instead... but, whatever... it's really hard to convince people even with the ol' 1/3*3 example I gave above where Decimal gives the wrong answer.;) So long as you are using enough scale, you'll probably do ok...:)

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

I came to programming from computational physics. In calculations I often used doubles instead of floats. double is probably the least used datatype in sql server.

create table test (MyMoney double precision, YourMoney tinyint)

I haven't yet come across a database that used it though.

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

Ben Leighton SSCommitted Points: 1838 More actions · Answer 3

cute point about vardecimal... thought about using it, but it doesn't ship with express or standard editions... shame!

Mauve SSChampion Points: 11316 More actions · Answer 4

Jeff Moden (6/26/2008)
JohnG (6/26/2008)
For financial systems, accuracy is paramount.
So I'd use DECIMAL
And, that is precisely why I'd use float instead... but, whatever... it's really hard to convince people even with the ol' 1/3*3 example I gave above where Decimal gives the wrong answer.;) So long as you are using enough scale, you'll probably do ok...:)

Speaking as a certified "bean counter" (yes, I have an accounting degree), the system has to be designed with the proper precision and scale for the monetary values that will be stored/computed/worked on. Rounding, when appropriate, is done. E.g., the "tax" on a purchase is rounded to (U.S.) pennies. Your mutual fund shares are only managed to 3 decimal places. Other systems, such as I worked on before, may require the full precision.

Therefore, know the datatypes and how they operate. Then make the appropriate choice working with the business experts when designing the system. Don't make an arbitrary choice -- it will come back to haunt you.

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

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 5

JohnG (6/27/2008)
yes, I have an accounting degree

Heh... nothing real evil meant here, John... but when I see something like that, I remember all the folks that I've interviewed that had CS degrees and/or MS certifications out the wahzoo that still couldn't write any kind of decent code. I've seen your posts in the past and, no doubt, you have some good experience in accounting and I'd trust that experience. But, just having a degree in something is no assurance of competency.

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

Mauve SSChampion Points: 11316 More actions · Answer 6

But, just having a degree in something is no assurance of competency.

Very true. With all disciplines, it is one thing to be "book smart", it is another thing to be able to apply that knowledge.

As for me, CS degrees didn't exist at the time (oops! I'm giving something away). The Business training and degree helped me to to use (apply) the [computer] "tools" in the proper manner.

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

noeld SSC Guru Points: 96590 More actions · Answer 7

jezemine (6/26/2008)
I came to programming from computational physics. In calculations I often used doubles instead of floats. double is probably the least used datatype in sql server.
create table test (MyMoney double precision, YourMoney tinyint)
I haven't yet come across a database that used it though.

In SQL Server, double = float(53)

* Noel

jezemine SSCrazy Eights Points: 8059 More actions · Answer 8

noeld (6/27/2008)
In SQL Server, double = float(53)

maybe so, but double precision looks better. like you are doing super-fine carpentry work or something, so you need to be doubly precise:

declare @chamfer double precision

set @chamfer = 2.883883838883880901111

select @chamfer/3.0

plus it's the only datatype I know of with a space in the name. how can you beat that?

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

mtlsql SSCommitted Points: 1528 More actions · Answer 9

I just played around with Jeff's example a little bit. It seems that all prime numbers exhibit this bad DECIMAL behavior, and that the bigger the prime number, the worse the output gets:

-- try with a bigger prime number...

DECLARE @dec DECIMAL (18,6) = 17, @flt FLOAT = 17;

SET @dec = 17;

SET @flt = 17;

SELECT 1/@dec * 17, 1/@flt * 17;

-- result 0.9999999999999999986

-- Assuming http://primos.mat.br/indexen.html is correct, use a BIG prime number...

-- The rounding error becomes more pronounced...

SET @dec = 104729

SET @flt = 104729;

SELECT 1/@dec * 104729, 1/@flt * 104729;

-- result 0.9999999999999941421

-- ...but DECIMAL seems to handle non-prime numbers okay...

SET @dec = 100000

SET @flt = 100000;

SELECT 1/@dec * 100000, 1/@flt * 100000;

-- result 1.0000000000000000000

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 10

Jeff Moden (6/26/2008)
JohnG (6/26/2008)
For financial systems, accuracy is paramount.
So I'd use DECIMAL
And, that is precisely why I'd use float instead... but, whatever... it's really hard to convince people even with the ol' 1/3*3 example I gave above where Decimal gives the wrong answer.;) So long as you are using enough scale, you'll probably do ok...:)

Having revisited several similar posts, I find that some of the automatic rounding SQL Server does in decimal and the 15 digit limitation of precision in float (and fact that it's a binary float made to look decimal) can pretty much screw you to the floor without warning with whatever you use. I'll never understand why they seem to have done it all the hard way. WTH... calculator chips on PCs are just a recent innovation, right? 486DXs are a brand new technology, right? :blink:

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

Jeff Moden (6/24/2008)
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
...
Again, my recommendation is that you make money calculations out to at least 4 decimal places and do the correct rounding if needed.

Hi Jeff,

I have to take issue with your extraordinary proof, as it is proof of nothing more than: converting a value to another precision and scale causes problems.

If you take a look at this slightly modified code and its results, you will see that your proof is allowing a conversion to a non-integer decimal in the final result (precision 9, scale 6 for @a), which is not the required result (precision 3, scale 0).

Once the result is converted back to the correct precision and scale, the result is correct.

DECLARE @a DECIMAL(3,0), @b-2 DECIMAL(18,0),@F FLOAT

SET @a = 3

SET @b-2 = 3

SET @f = 3

-- Check the types of the original values @a, @b-2, and @C

SELECT A AS A_VALUE, SQL_VARIANT_PROPERTY(A,'Precision') AS A_PRECISION, SQL_VARIANT_PROPERTY(A,'Scale') AS A_SCALE,

B AS B_VALUE, SQL_VARIANT_PROPERTY(B,'Precision') AS B_PRECISION, SQL_VARIANT_PROPERTY(B,'Scale') AS B_SCALE,

C AS C_VALUE, SQL_VARIANT_PROPERTY(C,'Precision') AS C_PRECISION, SQL_VARIANT_PROPERTY(C,'Scale') AS C_SCALE

FROM (SELECT @a, @b-2,@F) AS X(A,B,C)

-- Check the types and values coming out of the extraordinary proof

SELECT A AS A_VALUE, SQL_VARIANT_PROPERTY(A,'Precision') AS A_PRECISION, SQL_VARIANT_PROPERTY(A,'Scale') AS A_SCALE,

B AS B_VALUE, SQL_VARIANT_PROPERTY(B,'Precision') AS B_PRECISION, SQL_VARIANT_PROPERTY(B,'Scale') AS B_SCALE,

C AS C_VALUE, SQL_VARIANT_PROPERTY(C,'Precision') AS C_PRECISION, SQL_VARIANT_PROPERTY(C,'Scale') AS C_SCALE

FROM (SELECT 1/@A*3, 1/@B*3,1/@F*3) AS X(A,B,C)

-- Check the types and values coming out of the corrected proof

SELECT A AS A_VALUE, SQL_VARIANT_PROPERTY(A,'Precision') AS A_PRECISION, SQL_VARIANT_PROPERTY(A,'Scale') AS A_SCALE,

B AS B_VALUE, SQL_VARIANT_PROPERTY(B,'Precision') AS B_PRECISION, SQL_VARIANT_PROPERTY(B,'Scale') AS B_SCALE,

C AS C_VALUE, SQL_VARIANT_PROPERTY(C,'Precision') AS C_PRECISION, SQL_VARIANT_PROPERTY(C,'Scale') AS C_SCALE

FROM (SELECT CAST(1/@A*3 AS decimal(3,0)), CAST(1/@B*3 AS decimal(18,0)), 1/@F*3) AS X(A,B,C)

I wholeheartedly agree with your conclusion, though - use the correct data type, precision and scale for the job and perform calculations in float.

I cannot bear financials stored in float in the database as a simple check on SUM(amount) = total when using float will fail to match (a lot) because of the imprecision, so you end up either converting to a precise datatype or using a fudge such as ABS(SUM(amount) - total) < 0.01 just to *get around* the float problem.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 12

Same here, Magoo. Absolutely agree. The problem is that most people won't be aware and will write the code in the "extraordinary proof" manner.

I'll also state that they could have made our lives a bit easier here. Especially when it comes to division and multiplication.

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

Ash.Shah Ten Centuries Points: 1042 More actions · Answer 13

I have this scenario. Start with an integer value e.g. 365000. Over a period of time (usually a year) I will add one record a day with a proportional amount (so in this very basic example I would add one record each day of 1000 for the next 365 days). The total of these records should add up to the initial integer value. So each day I calculate how much of the initial total remains, and how many days are left to allocate it, and add a new record with the correct proportional amount.

Now this example is a gross oversimplification as a number of variables can change (including the initial value and the period), but the golden rule still applies - the sum of the records at the end of the period should equal the initial total. It goes without saying, I rarely have a nice easy integer value like 365,000 to start with!

As you might imagine, there will be all sorts of precision issues here. I have chosen to store my calculated data as float, as I believe this gives me the best chance of all my records adding up to the correct amount (or as near as possible!). Storing with any other type (decimal, money etc.) will necessarily compromise on precision and therefore adding up the records will almost certainly mean I will get a larger difference than if I store my results as floats.

I have read advice earlier in this thread suggesting I not store my data as floats. Given this scenario does that advice still stand? I don't think so but would like to hear any thoughts people have.

Thanks,

Ash

p.s. this is financial data

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Hi Ash,

It all depends !

If each individual value is required to be used in a rounded (e.g. to 2 dp for currency) form at any time, then using float is going to be a potential problem.

The way to ensure the total of all individual values always adds up to the original amount is to subtract the total so far from the original amount for the last period, no matter what method you use for interim values.

The problem with float is this:

e.g. 1000 over 3 days = 333.33, 333.33 and 333.34 when stored as decimal to 2dp.

e.g. 1000 over 3 days = 333.3333333333..., 333.333333333..., 333.33333333333 when stored as float.

I know which I would rather see in financial reporting.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Eric M Russell SSC Guru Points: 125594 More actions · Answer 15

A quotient is essentially a computed value. If the application is working with imprecise quotients, then perhaps it's best (from an accuracy, storage, and performance) to contain dividend and divisor in two simple integer columns.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Float vs Decimal?

Cookies on SQLServerCentral