February 12, 2008 at 9:31 am
Do you usually use the money or smallmoney datatype for US dollars in a table, or do you use a decimal datatype, and if so which. Any opinions on the advantages and disadvantages of each?
If you use money or smallmoney, do you usually add a constraint to make sure the value is even to the penny (no $24.3487 type amounts)?
February 12, 2008 at 10:01 am
I use decimal. It's simple, easy, and if we need to go to foreign currency later (and add a "type" col), I don't worry about it. Never saw the need for somehow enforcing money as it's own type.
I don't think there's a disadvantage, I just hate to tie myself down without any big benefit.
February 12, 2008 at 10:37 am
It's not like date fields where there are a lot of special functions. We just use decimal.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2008 at 9:42 pm
I've used Money... I've used SmallMoney... I've even used Decimal... and they all failed the accuracy test when it came to things like mortgage calculations.
If all you're ever going to do is add or substract like in a check book, then any of those datatypes will be fine. If you're ever going to multiply, divide, etc, then you need to use FLOAT and convert to a DECIMAL(x.2) datatype for display purposes, just like a financial or scientific calculator does... calculate to 15 digits, display only 12 or 13 or maybe even only 2. I learned the hardway... you don't have to...
DECLARE @F FLOAT
SET @F = .3333
DECLARE @D DECIMAL(4,2)
SET @D = .3333 --Simulates precision/scale lost during a calculation
SELECT Number,
[Number*@F] = Number*@F,
[Number*@D] = Number*@D,
[STR(Number*@F,4,2)] = STR(Number*@F,4,2),
[ROUND(Number*@F,2)] = ROUND(Number*@F,2),
[CONVERT(DECIMAL(4,2),Number*@F)] = CONVERT(DECIMAL(4,2),Number*@F)
FROM Master.dbo.spt_Values
WHERE Number <= 10
AND Type = 'P'
Pay attention to the differences in the 3rd and 4th columns and imagine just how much trouble you could get into when you're working with numbers in the thousands instead of just < 10 ... 😉
Lemme say it again... Display whatever way you want, but store and calculate FLOAT if you're going to do more than simple addition and subtraction to money (or any other decimal point bearing values, for that matter).
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2008 at 9:45 am
Jeff,
Doesn't the fact that floats are imprecise estimates represent a pretty major possible problem in using them in financial calculations? At least that's what I've always been told. I mean if you need more decimal places, add them on to the max, but possibly getting different results for the same calculation throws this into a bit of a question.
BTW, this isn't meant to throw rocks, I'm just trying to clarify my own confusion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2008 at 10:28 am
Grant Fritchey (2/13/2008)
Jeff,Doesn't the fact that floats are imprecise estimates represent a pretty major possible problem in using them in financial calculations? At least that's what I've always been told. I mean if you need more decimal places, add them on to the max, but possibly getting different results for the same calculation throws this into a bit of a question.
BTW, this isn't meant to throw rocks, I'm just trying to clarify my own confusion.
Heh... no, I know, Grant... didn't take it as rock throwing because I was shocked and mortified when someone first made the FLOAT suggestion to me. In fact, I did start throwing rocks, boulders, cars, anything I could get my hands on, the first time someone even suggested using FLOAT.
I don't see any harm in something like DECIMAL(28,15) (takes 13 bytes of storage)... just don't do any multiplication or division on 2 place datatypes for money calculations... the previous code I showed clearly demonstrates why.
So far as FLOAT goes, most of the functions that you might care to use on some high level math for money or other things (like LOG, Sin, Cos, etc) that use decimal places, converts the operands (and sometimes the result) to FLOAT anyway... storing the data as FLOAT gets rid of just one more implicit conversion.
And, consider this... I used to use the following to show that FLOAT was a bad thing...
DECLARE @Float1 FLOAT
DECLARE @Float2 FLOAT
SET @Float1 = 10
SET @Float2 = 1
SELECT @Float2/@Float1
...Now, if I do the following, do you agree that the constants are implicitly converted to FLOAT?
SELECT 1.0/10.0
So, why doesn't the answer come out to be 0.10000000000000001 like in the preceding code? The answer is that there's also some implit rounding to 6 decimal places. Guess what you get if you do the calculation in all FLOAT and round to 6 decimal places?
DECLARE @Float1 FLOAT
DECLARE @Float2 FLOAT
SET @Float1 = 10
SET @Float2 = 1
SELECT CAST(@Float2/@Float1 AS DECIMAL (15,6))
Use DECIMAL if you want... might even be able to get away with the 4 decimal places of the MONEY datatype... Just make sure that you carefully consider how many decimal places you need if you need to do anything but add and subtract. Me? It took a really long time for folks to make me realize that everything involving any kind of decimal math should be done all in FLOAT and converted to Decimal only for display purposes, but I've learned my lesson by loosing way too many important pennies...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 8:03 am
Regarding decimal and float types:
Decimal works well except there is no support for this in Delphi v. 6.0. (Something to do with support for ADO.) You may encounter similar problems in other environments.
As for floats, I had the following bad experience:
A company has 99 shares and 3 shareholders, each holding a third of the stocks.
So, entering 33.33 shares for the first investor,
then 33.33 for the second, so far so good.
But inserting the amount of shares for the third shareholder was an absolute nightmare. There was always an nth decimal that either forced the system to advise that:
1. there were not enough shares for the last issuance (sh# 3: 33.33)
2. or there was always a minuscule fractional quantity of shares left unissued.
The user had to use trial and error to type in the last amount just right.
Floats as implemented always have a minuscule fractional tail. It causes the failure of tests like:
var sMessage: string;
TotalQty, QtyIssued: double;
TotalQty := 99.99;
QtyIssued := 99.99;
if TotalQty - QtyIssued = 0 THEN
sMessage = 'All shares issued'
else if TotalQty - QtyIssued < 0.0 THEN
sMessage = 'Not enough shares for this transaction"
ELSE
sMessage := 'Unissued shares left';
END
February 14, 2008 at 9:42 pm
So... what would you do with 99.99 shares and 4 people? Decimal isn't going to hack that, either.
Implementation of float in your 3 person case would be as follows...
DECLARE @TotalShares FLOAT
DECLARE @People FLOAT
SET @TotalShares = 99.99
SET @People = 3
SELECT STR(@TotalShares/@People,5,2)
Like I said... do all the calculations using FLOAT... format the output for the desired scale and precision.
For the 4 person example using 99.99, you're gonna need a lot more scale than just "2" decimal places whether it's with float or decimal. Or, let it do the proper rounding to 100.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 11:31 pm
I would say use whatever datatype is appropriate - if the potential range of stored values falls within smallmoney then go for it. Decimal is useful in situations where you may want to work with un-rounded values with accuracy ... interest rate and exchange rate calculations might require 16 decimal places.
Regardless of type, the business rules should dictate the constraints - if you need to store values rounded to the nearest cent then enforce it.
February 15, 2008 at 6:48 am
The biggest issue we've had with using money datatypes is that reports in Crystal, and other tools, put a $ sign on each foeld by default. Given tha tI support multi-currency systems, this is a problem. We get calls all the time about foreign currency reports that have $ signs. The rounding issues occasionally bite us, but we've pu a lot of effort into handling those situations.
February 15, 2008 at 7:07 am
Jeff Moden (2/14/2008)
for the 4 person example using 99.99, you're gonna need a lot more scale than just "2" decimal places whether it's with float or decimal. Or, let it do the proper rounding to 100.
that's actually what I would usually do. Store it as decimal with 4-5 after the ".", and show them just the 2.
Any of the solutions work as long as your audience understands it. If you have math-savvy folks, then you have no issues one way or the other.
On the other hand, there are lots of people, including a lot in the Finance department, who have no understanding of rounding (and the problem you might find because of it), or any care to understand it. Sometimes you just have to know that there's a rounding error built in, and just bite your lip when the ultimate reliability test is whether adding up the rows matches adding up the columns. I've had lots of the higher-ups state specifically that, usually having observations as to my competence when I can't "get rid of the 1 penny difference" just like 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?
February 15, 2008 at 7:15 am
matt stockham (2/14/2008)
I would say use whatever datatype is appropriate - if the potential range of stored values falls within smallmoney then go for it. Decimal is useful in situations where you may want to work with un-rounded values with accuracy ... interest rate and exchange rate calculations might require 16 decimal places.
Heh... it's deja-vue all over again... and a bit ironic... that's exactly what I used to recommend... (Matt, PLEASE take no offense... I'm NOT picking on you... it's just what I used to say almost word for word 😉 )
Then, I found out how inaccurate that recommendation is... it's really hard to convince people of the error of their ways on this type of thing... I know because someone had to beat me in the head about once a month until I ran across the "proof" code I posted previously (posted below, as well, just for convenience). But, rather than turn this into any kind of a war, I'll just give my recommendation one more time and then go away... for any type of decimal math, use FLOAT to do the calculation and convert the answer (only) to decimal with the correct number of decimal places only if you need display the answer. I only ask that you study and analyze my code example with an open mind and see what I mean...
Here's the code that made me realize the error of my ways and to always use FLOAT for decimal point math... again, compare the results of the 3rd and 4th columns... real head turner if you actually spend the time to see what's happening and why in the code...
All the code does is multiply the DECIMAL and FLOAT versions of the number .3333 times an INTEGER. Notice in column 3 how early (3rd row) the DECIMAL answer departs from the correct answer...
DECLARE @F FLOAT
SET @F = .3333
DECLARE @D DECIMAL(4,2)
SET @D = .3333 --Simulates precision/scale lost during a calculation
SELECT Number,
[Number*@F] = Number*@F,
[Number*@D] = Number*@D,
[STR(Number*@F,4,2)] = STR(Number*@F,4,2),
[ROUND(Number*@F,2)] = ROUND(Number*@F,2),
[CONVERT(DECIMAL(4,2),Number*@F)] = CONVERT(DECIMAL(4,2),Number*@F)
FROM Master.dbo.spt_Values
WHERE Number <= 10
AND Type = 'P'
For those that don't want to take the time to run that snippet of code, here's what it generates... notice that none of the incorrect answers appear in column 4 (calculated by float, displayed by STR) or column 6 (calculated by float, displayed by Decimal).
Number Number*@F Number*@D STR(Number*@F,4,2) ROUND(Number*@F,2) CONVERT(DECIMAL(4,2),Number*@F)
------ ------------------- --------- ------------------ ------------------- -------------------------------
0 0.0 .00 0.00 0.0 .00
1 0.33329999999999999 .33 0.33 0.33000000000000002 .33
2 0.66659999999999997 .66 0.67 0.67000000000000004 .67
3 0.99990000000000001 .99 1.00 1.0 1.00
4 1.3331999999999999 1.32 1.33 1.3300000000000001 1.33
5 1.6664999999999999 1.65 1.67 1.6699999999999999 1.67
6 1.9998 1.98 2.00 2.0 2.00
7 2.3331 2.31 2.33 2.3300000000000001 2.33
8 2.6663999999999999 2.64 2.67 2.6699999999999999 2.67
9 2.9996999999999998 2.97 3.00 3.0 3.00
10 3.3329999999999997 3.30 3.33 3.3300000000000001 3.33
(11 row(s) affected)
Notice that the column names show what the formula calculation is...
@D = decimal version of .3333
@F = float version of .3333
Number = an integer
Obviously, columns 4 and 6 are the only columns with correct answers... if you look at their formulas, they follow my recommendation... do the calculation in float, convert to decimal to display the answer (of course, STR is a formatter for float).
Also note that ROUND doesn't work real well on FLOAT and probably shouldn't be used with FLOAT. 😉
EDIT: Sorry folks... had a couple of typos where I has "should" instead of "Shouldn't"
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 7:24 am
Ross McMicken (2/15/2008)
The biggest issue we've had with using money datatypes is that reports in Crystal, and other tools, put a $ sign on each foeld by default. Given tha tI support multi-currency systems, this is a problem. We get calls all the time about foreign currency reports that have $ signs. The rounding issues occasionally bite us, but we've pu a lot of effort into handling those situations.
Just curious... is there no way to change that default?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 7:46 am
Jeff Moden (2/15/2008)
Ross McMicken (2/15/2008)
The biggest issue we've had with using money datatypes is that reports in Crystal, and other tools, put a $ sign on each foeld by default. Given tha tI support multi-currency systems, this is a problem. We get calls all the time about foreign currency reports that have $ signs. The rounding issues occasionally bite us, but we've pu a lot of effort into handling those situations.Just curious... is there no way to change that default?
Yes. You can tell Crystal to format monetary (and other data types) in a variety of ways. I haven't done it in years, but I did a lot of work with Crystal in 2004/05, and I remember solving this exact problem. How? No clue. But I did solve it. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2008 at 7:53 am
Jeff, thank you for the recommendation on using float instead of money for complex calculations. I see what you're talking about on that one. I've used money instead of float before this, but I'll definitely change.
On the other hand, I have a function that calculates linear distance between sets of latitude and longitude coordinates, and every now and then, it craps out because of the float rounding problems. Ends up trying to find the square root of a negative value (-0.000000001), when it really should be dealing with 0. Had to build in extra error-catching steps to deal with that.
Even in that case, using float instead of decimal resulted in a measureable speed increase. (I have to calculate distances between sets of up to 4-million records and sets of up to about 1,000 records, as a cross-join. Every millisecond per record counts at that point.) Just have to be aware that the rounding issue can bite you, and make sure to correct for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply