SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Thursday, June 15, 2006

Numeric or Int to store prices

SQL Apprentice Question
am upgrading tables on a legacy SQL Server 2000 database
Some price data is stored in real datatypes, which is not correct.
Prices are in Euros and I just need 2 decimals.

I could convert them to numeric or use integer multiplying values per
100 then dividing on them on the presentation layer.


I think it could be faster, take less space and also more cross
compatible.


Is this a correct practice ?

Why should one avoid the MONEY data type for currency?


Celko Answers
>> Prices are in Euros and I just need 2 decimals. <<


There are websites with the rules for Euro conversions (in particular,
look at triangulation). I would use NUMERIC (n, 4) to be safe. And
you already know to avoid FLOAT, REAL, MONEY and SMALLMONEY data types.


>> Why should one avoid the MONEY data type for currency? <<


1) They are proprietary

2) They are proprietary


3) The MONEY datatype has rounding errors. 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

No comments: