Should I use money column to store amount related columns

Question

Should I use money column to store amount related columns

shamshad.ali

Hall of Fame

Points: 3850
More actions
September 3, 2015 at 5:59 am

#300164

I just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this suggestion? Need experts suggestion.
Regards,
Shamshad Ali

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I usually avoid the money data type because it has some weird rounding issues. I prefer to use decimal in general. You can google for advantages and disadvantages on both.

Here are some results from a basic google search:

http://blog.learningtree.com/is-money-bad-the-money-datatype-in-sql-server/

http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx

http://sqlstudies.com/2014/06/02/what-is-the-difference-between-money-and-decimal194/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

I'm with Luis. The money data type seems attractive, but it just leads to headaches. Use decimals and keep going.

The only issue around that from a data standpoint is that you can define the type of money you're dealing with in the data structure. This can act as protection for your systems, but I'm pretty sure the that it's not worth it in most cases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

I agree with Grant and Luis. I'll also tell you that if you store the calculations from interest calculations for things like amortization tables, don't just store the data with 2 decimal places. Store at least 15 decimal places for calculations. During interest calculations, remember to convert anything that may have fewer decimal places to at least 15 decimal places. And for goodness sake, don't use FLOAT anywhere because it has a maximum precision of "only" 15 decimal places.

Be off by one penny anywhere or suffer bad rounding by 1 penny anywhere and Granny and her spreadsheet will take you to court.

--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)