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
Thursday, June 15, 2006
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment