speed of money versus decimal

[Henrik Staun Poulsen]

Dear all,

Today I had a consultant claiming that he had found that the money
data type was "a lot faster" than the decimal data type.
Can you confirm this?

We have also tried to implement our own data type ("decimal integer"),
for temperature data etc., which has a range of -3276.8 to +3276.7.
The beaty of this was that only 2 bytes (smallint) was needed to store
the data.
Unfortunately this turned out to be a lot slower than using the
decimal data type, so we had to drop this data type.

Is money faster than decimal, and does this include SmallMoney as
well?

TIA
Henrik Staun Poulsen
www.sql.udstyr.dk

[--CELKO--]

>>Is money faster than decimal, and does this include SmallMoney as well? <<

1) I doubt it

2) They are being deprecated, so who cares?

3) In the meantime, they give bad results in calculations -- Google
around for some examples that involve division and multiplication.

4) They fail to meet the rules for GAAP in the United States and Euros
in the EU.

[TheSQLGuru]

2) do you have a link showing that Microsoft is deprecating them from SQL
Server?

3) Same here - too many result came back in my searches to find a SQL Server
example for this error you mention.

4) Can you please provide a bit of detail here? Is it precision, max/min,
what? Just curious since a true limitation here is something I would like
to advise my clients of.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:c47ae74c-24aa-4146...@e1g2000hsh.googlegroups.com...

[TheSQLGuru]

I was able to find money division errors, due to precision. Thus if users
made decimal (19,4) for instance they would have the same error. decimal
only helps if it contains sufficient precision to allow for further accuracy
beyond 4 places.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:c47ae74c-24aa-4146...@e1g2000hsh.googlegroups.com...

[Henrik Staun Poulsen]

Dear --CELKO--,

> 2) They are being deprecated, so who cares?

I hope not. Where did you see this? It works in SQL 2008 November CTR.

> 3) In the meantime, they give bad results in calculations -- Google
> around for some examples that involve division and multiplication.

Again I hope this is not a frequent error.
Do you have any SQL code that shows this?

TIA
Henrik Staun Poulsen

[Jamie Collins]

On Dec 10, 11:38 pm, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
> I was able to find money division errors, due to precision. Thus if users
> made decimal (19,4) for instance they would have the same error. decimal
> only helps if it contains sufficient precision to allow for further accuracy
> beyond 4 places.

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/af6861dc4d5e0705

declare @m money
declare @d decimal(19,4)

set @m = 12.39
set @d = 12.39

select (@m/1000)*1000
select (@d/1000)*1000

Jamie.

--

[Henrik Staun Poulsen]

Jamie,

Thank you very much for pointing me to that disguession.

<<The reason decimal works better than money is precisely because it
types
<<expressions with additional precision as needed. Money expressions
are
<<typed as money, which is the problem.

This may explain why money might be faster than decimal.
All the casting will take time.

It may also explain why our home-grown data types are even slower, I
would not be surprised if CLR did a lot of casting.

Best regards,
Henrik Staun Poulsen
www.jagt.udstyr.dk

[--CELKO--]

The MONEY data type 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

Google "Euro" and "Triangularization" for the decimal place rules --
basically you have to covert one currency to Euros then convert the
Euros to the target currency. Things have to be six decimal places
for large amount, but only five for smaller amounts (I am not sure
about all the current rules).

[Jamie Collins]

On Dec 17, 2:23 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> Google "Euro" and "Triangularization" for the decimal place rules

I think you mean "Triangulation" e.g. see:

Euro myths and facts
http://www.sysmod.com/irc98b13.htm

Jamie.

--