Numeric or Int to store prices

Hi,
I 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 ?
Thank you
FredHi,
Have you tried using cast or convert (to money data type). You could use
this within a select statement. If you aren't a fan of the money data type
try using convert with numeric/decimal data type, you can specify scale and
precision.
Ray
"frederic@.naar.com" wrote:

> Hi,
> I 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 ?
> Thank you
> Fred
>|||>> 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.|||CELKO - Why should one avoid the MONEY data type for currency?
"--CELKO--" wrote:

> 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|||Guess I have to go and make some changes now then.
I'm in real need of a sql do's and don'ts list.
Thanks for the lesson.
"--CELKO--" wrote:

> 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
>|||Thank you Celko,
I agree with you, money and Int are not adapted for this type of
calculations and it is important to realize that and remember to cast
the values to float before calculation as follows
SET @.money_result = CAST(@.my_part AS FLOAT)/ CAST( @.total_amt AS
FLOAT)* CAST(@.gross_amt AS FLOAT)
instead of
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt

>From what I can see smallmoney and money are actually integers (and
bigint) with a predefined number of decimal places,
On the other hand numerics are actually sequences of bytes (5,9,13,17)
with a predefined number of decimal places too so I understand the
system stores the first byte for sign and decimal places and in every
successive byte the values 0 to 99, similar to what you would do with
chars. I understand the database then casts the numerics to floats to
perform calculations
So a numeric (5,0) will be able to store values 0 to 99,999,999 (or
99.99.99.99 in bytes)
and a numeric (5,4) will store 0 to 9,999.9999 while occupying 5 bytes
against 4 bytes for a smallmoney which will allow you to store values 0
to 214,748.3647
this is 20 times more.
To store the same value in a numeric field you would use 9 bytes vs. 4
bytes so 225% more space and I need to store some million records.
Space is not an issue in general but can be a performance issue for the
8k database pages...
So as I needed 2 decimals only i figured out I could use integers to
store the values multiplied by 100 while remembering to cast to floats
in case of calculations.
this would allow me to store values up to 21,474,836.47 in 4 bytes vs.
9 bytes.
Also integers are not proprietary.
Is it acceptable to use this approach?
Thank you
Fred|||Hi Fred, see inline
frederic@.naar.com wrote:
> Thank you Celko,
> I agree with you, money and Int are not adapted for this type of
> calculations and it is important to realize that and remember to cast
> the values to float before calculation as follows
> SET @.money_result = CAST(@.my_part AS FLOAT)/ CAST( @.total_amt AS
> FLOAT)* CAST(@.gross_amt AS FLOAT)
> instead of
> SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt
Using the second SET statement is not a problem at all, provided
@.my_part, @.total_amt and @.gross_amt are decimals (or one of them is). As
long as the result fits the maximum possible definition of a decimal,
there will be no loss of precision.
But the data type of @.money_result is important. With the (implicit)
cast to its data definition, the value could lose precision. But in that
case, the rounding will be done on the end result, not on any
intermediate result.

> bigint) with a predefined number of decimal places,
> On the other hand numerics are actually sequences of bytes (5,9,13,17)
> with a predefined number of decimal places too so I understand the
> system stores the first byte for sign and decimal places and in every
> successive byte the values 0 to 99, similar to what you would do with
> chars.

> I understand the database then casts the numerics to floats to
> perform calculations
No it does not. What information made you reach that conclusion? A float
can not always represent a decimal value. This means a float cannot be
guaranteed to be correct when converting back to a decimal after one or
more calculations.

> So a numeric (5,0) will be able to store values 0 to 99,999,999 (or
> 99.99.99.99 in bytes)
No it won't. A numeric(5,0) can store up to 5 digits, not 8. I think you
are confusing the data definition and with the report about byte use.

> and a numeric (5,4) will store 0 to 9,999.9999 while occupying 5 bytes
> against 4 bytes for a smallmoney which will allow you to store values 0
> to 214,748.3647
> this is 20 times more.
> To store the same value in a numeric field you would use 9 bytes vs. 4
> bytes so 225% more space and I need to store some million records.
A decimal (9,0) will use 5 bytes and can store the values -999,999,999
through 999,999,999
A decimal (9,4) will use 5 bytes and can store the values -99,999.9999
through 99,999.9999
A smallmoney will use 4 bytes and can store the values -214,748.3648
through +214,748.3647
So your statement is true, but only if you nee values between 100K and
214K and have no values larger than 214K. That is a pretty narrow
margin. So for all practical purposes, the difference is only 1 byte (5
bytes versus 4 bytes, or 9 bytes versus 8 bytes).

> Space is not an issue in general but can be a performance issue for the
> 8k database pages...
> So as I needed 2 decimals only i figured out I could use integers to
> store the values multiplied by 100 while remembering to cast to floats
> in case of calculations.
No, it is a bad idea to cast to floats. If you must cast, then cast to
decimal.

> this would allow me to store values up to 21,474,836.47 in 4 bytes vs.
> 9 bytes.
> Also integers are not proprietary.
> Is it acceptable to use this approach?
You could do this. And it would work. But your documentation needs to be
very good, otherwise one day someone will make the mistake to 'forget'
to divide by 100, forget to cast it to decimal *before* the division, or
enter an amount in the assumption that it is a dollar/euro amount
instead of dollarcents/eurocents.
I would recommend against it. I would recommend using decimal or (if you
must) money/smallmoney, because that will not lead to incorrect
interpretation.
Chances are that you will never see any performance degradation.
Typicall, a money amount is only one of the columns in a table, and
usually one of the narrower columns. Proper indexing and regular
defragmenting will probably bring more performance gain then saving a
few bytes for a row.
HTH,
Gert-Jan

> Thank you
> Fred|||Thank you Gert-Jan
See in-line
> No it does not. What information made you reach that conclusion? A float
> can not always represent a decimal value. This means a float cannot be
> guaranteed to be correct when converting back to a decimal after one or
> more calculations.
I thought 1 byte per digit, mist be a char derived field... wrong
assumption... then I messed up with the bytes with the data definition.

> A decimal (9,0) will use 5 bytes and can store the values -999,999,999
> through 999,999,999
> A decimal (9,4) will use 5 bytes and can store the values -99,999.9999
> through 99,999.9999
> A smallmoney will use 4 bytes and can store the values -214,748.3648
> through +214,748.3647
My problem is the application uses several currencies and some are
Japanese Yen or Indonesian Rupiahs and such now 1 euro = 145.23 yen and
I have items costing up to 10,000 euros which would make 14 million
yen...then I need a 2 decimals precision for other currencies so that
makes a decimal(10,2) but then it still wont work with rupees which
might go over 140 million...
> I would recommend against it. I would recommend using decimal or (if you
> must) money/smallmoney, because that will not lead to incorrect
> interpretation.
At the end I agree with you, it is not worth the effort, a decimal
(18,2) or (18,4) could do the job with 5 bytes more in my tables than
an integer, and will allow me to manage any range I foresee
thank you for helping me sort this doubt!
Fred