Which data types for currency

SQL Apprentice Question
know to many of you this will sound like a daft question, but what are the
best data types for storing currency values (assuming I'm going to be
performing arithmetic operations on them and don't want crazy rounding
issues)?


Obviously 'float' & 'real' are out of the question, but I read somewhere a
while ago that 'money' and 'smallmoney' are not good choices either, and
that everything should be 'decimal'?


Is that true? If so, I've been using the wrong datatypes for some of my
applications for a good 5 years now (although I've never noticed any
rounding issues) and it would beg the question, what are 'money' and
'smallmoney' good for in that case?


What do you guys use for storing and manipulating monetary values, and why?


Celko Answers
>> but I read somewhere a while ago that 'money' and 'smallmoney' are not good choices either, and that everything should be 'decimal'? Is that true? <<


Yep! The MONEY datatype has rounding errors as well as destroying
portability Using more than one operation (multiplication or division)
on money columns will produce severe rounding errors. A simple way to
visualize money arithmetic is to place a ROUND() function calls after
every operation. For example,

Amount = (Portion / total_amt) * gross_amt


can be rewritten using money arithmetic as:


Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)


Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.


BEGIN
DECLARE @gross_amt MONEY,
@total_amt MONEY,
@my_part MONEY,
@money_result MONEY,
@float_result FLOAT,
@all_floats FLOAT;


SET @gross_amt = 55294.72;
SET @total_amt = 7328.75;
SET @my_part = 1793.33;


SET @money_result = (@my_part / @total_amt) * @gross_amt;
SET @float_result = (@my_part / @total_amt) * @gross_amt;
SET @Retult3 = (CAST(@my_part AS FLOAT)
/ CAST( @total_amt AS FLOAT))
* CAST(FLOAT, @gross_amtAS FLOAT);


SELECT @money_result, @float_result, @all_floats;
END;


@money_result = 13525.09 -- incorrect
@float_result = 13525.0885 -- incorrect
@all_floats = 13530.5038673171 -- correct, with a -5.42 error


I would also talk to your accounting department about GAAP rules if you
are in the US and the rules for Euro conversions if you are overseas.