Decimal or Currency data type.... -- sql server programming

Steve Kass
1/15/2004 2:50:08 AM

Gary,

Currency (money) is fine if all you need to do is add and subtract.
If you will be multiplying and dividing (especially in the other order),
I'd recommend decimal(19,4), otherwise you might see serious
inaccuracies. Here's a simple example of where using money creates a
quick error of almost 1%, but where decimal does not:

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

set @m = 12.39
set @d = 12.39

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

-- Steve Kass
-- Drew University
-- Ref: 9647763D-823F-46CB-9704-18F15D16E687

[quoted text, click to view]

Joe Celko
1/15/2004 9:50:42 AM

Go with DECIMALK(s,p). MONEY is a proprietary datatype; DECIMAL(s,p) is
Standard SQL. MONEY has slightly different rounding and truncation
rules than DECIMAL(s,p).

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Louis Davidson
1/15/2004 12:40:12 PM

Actually, there is not an error here (exactly.) It just seems that way.
Arguments could be made that the decimal version is actually incorrect.

Now that you all think I am stupid, let me explain.

There are four significant digits to each of the numbers we are dealing
with. Math is done in a particular order, dealing with the number of digits
in a particular order, in this case you have forced a given order.

Okay, what is 12.39 / 1000? .0123900 right? Well, yes and no. Dealing
strictly with a datatype that only allows 4 significant digits to the right
of the decimal point, the answer is: .0123. You can guess what happens
next, .0123 * 1000 = 12.30.

I am more interested in how the decimal (19,4) answer comes out with the
"right" answer. When I execute this, the answer is 12.390000000. This is
not a reasonable decimal (19,4) answer, since when I cast this answer to a
decimal (19,4) It is :

select cast((@d/1000)*1000 as decimal(19,4))

I get back:

12.3900

The right answer. But what if we make sure that every intermediate result
is (19,4)?

select cast( cast((@d/ cast(1000 as decimal(19,4))) as decimal(19,4)) *
cast(1000 as decimal(19,4))as decimal(19,4))

returns:

12.4000 --if I didn't cast the final result to a decimal still I got back
12.4000000

Better than 12.39 in some ways, but I dont want to go through the math to
see why it rounded.

So what is right? Well, first off I hope that you are not doing a whole lot
of this kind of tricky math in your actual application. Second, what does
your requirements require. Under normal cases, say taking 10% off of the
value of a product:

select @m * cast(.9000 as money)
select @d * cast(.9000 as decimal(19,4))

The answers are pretty much exactly the same. Note that I had to cast .9 as
the proper datatype to make it fair because each of the calculations started
to think that it was a real value.

---------------------
11.1510

----------------------------------------
11.1510000

In fact, if we go back to the original calculation and use reals instead of
integers for 1000:

select (@m * 1000.0) / 1000.0

We get the answer that you expected: 12.39000000000 except for one thing,
the final outcome is a floating point value, not a money value (this is
about data type implicit conversion precedence, which I am sure is
documented somewhere :). If this had been one of those ugly values that are
really hard to store as a float value, take 1/10 for example:

select cast(1.0/10.0 as float)

returns:

0.10000000000000001

you are possibly back in the same boat. The thing I am trying to say is
that when dealing with monetary values, it can be imperative to be very
careful with rounding/truncation issues.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]

Steve Kass
1/15/2004 2:10:33 PM

Louis,

By "error", I meant a result different from the mathematically exact
result, assuming the input values were exact. I don't think there are
any situations where decimal gives a worse answer than money, but I
would be happy to be proven wrong. None of the non-integer SQL number
types are "exact", and the more you know, the better.

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.

SK

[quoted text, click to view]

Louis Davidson
1/15/2004 3:40:30 PM

Yeah, I know, it is probably just the word "error" that got me started :)

Money expressions are not necessarily typed as money, however. In the
example, it is just that:

Decimal (19,4) * Int = Decimal (?,?)

But

Money * Int = Money

It is actually more predictable. My point was simply that it was really a
question of what is desired. When I did the operation with purely Decimal
(19,4) factors, the result was different, but just because of rounding
performed rather than truncation for money.

The problem with math on computers and especially SQL, is that it is really
easy to "goof" and use an integer value some of the times, and a real
others, just because it appears to the average non-computer-nerd that the
following expressions are all exactly the same values:

select 2.5 * 10
select (2 + 1 / 2) * 10
select 5 / 2 * 10
select 2.5 * 10.0

The are all 2 and one half * 10, essentially. But here in nerdville, we
know that 5/2 = 2, not 2.5 since 5 and 2 are integers. Same thing with 2 +
1/2. It equals 2 because 1/2 = 0.

Sorry, just having a little nerd fun!

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Decimal or Currency data type....