**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:

Post a Comment