Money vs. Decimal Data type

Question

Money vs. Decimal Data type

meichner

SSCertifiable

Points: 5563
More actions
July 31, 2008 at 10:00 am

#126720

I am a c# program who uses SQL Server 2005 for my database. I had read a while back in another forum that using a Data Type of Money in my database may cause me some problems in my T-SQL code as well as my c# program. Is this true? I would appreciate answers/opinions to the following:
I need to stored prices in US dollars in my application. What is the best datatype to store these prices in my database and in my c# code?
Thanks

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think the money data type unless you are working with the federal debt.

The main issue is using currency types in an application as I believe they will use the region values set on the PC so it could $100 on a us PC and 100 euros on a french PC.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

meichner SSCertifiable Points: 5563 More actions · Answer 2

Thanks. To be on the safe side do you see any problems using a decimal data type instead of money for US Dollars?

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

It depends... if you're not going to do anything to the values other than add or subtract... no problems. If you're going to do anything else (like multiply or divide), you'll need at least 4 decimal places to do the calculations without loosing overall accuracy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichner SSCertifiable Points: 5563 More actions · Answer 4

I was under the impression that one of the reasons that you use a decimal data type instead of a float is to avoid rounding issues. Does Money avoid rounding issues?

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Most people think that FLOAT makes rounding issues... lemme as this... if you divide 1 by three and then multiply the answer times 3, what is the answer supposed to be? If you use DECIMAL(x,2), you probably won't get the answer you want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 6

Jeff Moden (8/1/2008)
It depends... if you're not going to do anything to the values other than add or subtract... no problems. If you're going to do anything else (like multiply or divide), you'll need at least 4 decimal places to do the calculations without loosing overall accuracy.

The MONEY type has 4 decimal places - but the real answer depends on the degree of accuracy you need. Be careful about implicit conversions involving money if you need greater precision than 4 decimal places: http://tinyurl.com/59s2dn

There are also subtle performance differences between the two types - Aaron Betrand blogged about these (targeted at SQL Server 2008 but should hold fairly true for previous versions): http://sqlblog.com/blogs/aaron_bertrand/archive/2008/04/27/performance-storage-comparisons-money-vs-decimal.aspx

Regards,

Jacob

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

The big thing missing from Aaron's blog is the code he used for each test. 😉 Didn't see much on accuracy, either...

I agree about the MONEY datatype, though... I'd just as soon it weren't available...

DECLARE @dOne DECIMAL(20,4),

@dThree DECIMAL(20,4),

@mOne MONEY,

@mThree MONEY,

@fOne FLOAT,

@fThree FLOAT

SELECT @dOne = 1,

@dThree = 3,

@mOne = 1,

@mThree = 3,

@fOne = 1,

@fThree = 3

SELECT @dOne/@dThree*@dThree AS DecimalResult,

@mOne/@mThree*@mThree AS MoneyResult,

@fOne/@fThree*@fThree AS FloatResult

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichner SSCertifiable Points: 5563 More actions · Answer 8

Thanks to both Jeff and Jacob for the help. Based on your posts I will use decimal instead of money. I was wondering, since I am only working with US dollars would DECIMAL(15,5) be a safe bet in order to avoid rounding issues?

Thanks

ps. At this point in my project my boss has not decided what we are going to do with the dollar amounts. We may just be adding and subtracting them, but I can't be sure that at some point we are not going to multiply or divide the dollar amounts.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

meichner (8/5/2008)
Thanks to both Jeff and Jacob for the help. Based on your posts I will use decimal instead of money. I was wondering, since I am only working with US dollars would DECIMAL(15,5) be a safe bet in order to avoid rounding issues?
Thanks
ps. At this point in my project my boss has not decided what we are going to do with the dollar amounts. We may just be adding and subtracting them, but I can't be sure that at some point we are not going to multiply or divide the dollar amounts.

Thanks for the feedback.

I'd have to say, "IT Depends" so far as the number of decimal places go. If you're doing long term mortgage caclulations, I'd be tempted to use something a bit more than 5 decimal places... you'll probably think I'm whacked, but I use FLOAT for money calculations (much more accurate than you think) and then round the answers for display... kinda like a calculator does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichner SSCertifiable Points: 5563 More actions · Answer 10

I am in the manufactoring industry. We are more concerned with billing and inventory issues. Hopefully 5 places will be okay.

Thanks for the advice. It was an eye opener to find that decimals are subject to rounding issues. I took a seminar a while back where they suggested using decimals and money to avoid rounding issues. I guess they were wrong.

Thanks again.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

meichner (8/6/2008)
I took a seminar a while back where they suggested using decimals and money to avoid rounding issues. I guess they were wrong.

Not exactly. The problem is not quite as bad as Jeff's demonstration makes it seem. As he mentions, adding and subtracting money is fine -- the problem appears when you multiply or divide. But when is a money value ever multiplied or divided by another money value? If you were designing a Money type in Java, C++ or C#, you would catch such operations and throw an exception.

Change @mThree in the test script to Float, Decimal or Numeric and the correct answer is returned. Changing it to Int also returns a rounding error, but we always expect that when using Int in any division.

Thus,in operation, Money shouldn't be giving you any rounding problems.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Heh... provided that an inter rate, tax rate, discount rate, or any other rate is never used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

meichner (8/6/2008)
I am in the manufactoring industry. We are more concerned with billing and inventory issues. Hopefully 5 places will be okay.
Thanks for the advice. It was an eye opener to find that decimals are subject to rounding issues. I took a seminar a while back where they suggested using decimals and money to avoid rounding issues. I guess they were wrong.
Thanks again.

Like it or not, EVERYTHING is subject to rounding issues. Floats will too, if you just try hard enough.

It's really a matter of packaging. The flaw is expecting that you'd get the same results from those different types ( if they acted the same, there wouldn't be much use for them, would there?) It's simply a matter of know WHY it may act a certain way, and WHAT might make it act that way.

Also - this is one of those things where going back to that class long forgotten from high school about basic ways to reduce the amount of error you introduce into your results with devices like calculators and computers. The golden rule there: do your multiplies FIRST, then the divides (or more accurately described as: if you have two permutative operations, and one is going to decrease the intermediate result, and the other increase the result, then do the increasing one FIRST, and then the decreasing one, so as to not compound your margin of error),

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

Heh... reminds me of the ol' hand assembly days... "Shift Left, Load Zeros". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)