| Author |
Topic |
|
fizzer666
Starting Member
6 Posts |
 Posted - 2006-07-06 : 06:16:51
|
| Hi,I am really confused as to when I should use the money data type in SQL Server. I develop in VB.Net and want to store basic accounting figures (ie customer balances etc..) I will need to add / subtract amounts and calculate percentages of amounts.I started to use the SMALLMONEY type because I found a post saying that I must use the money type for all accounting data. However, I have found many conflicting ideas as to whether I should use decimal or money.What are the advantages / disadvantages of using Decimal over money or vise versa? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-06 : 06:26:51
|
| money is 4 dec places and also has some specific rounding.If you want to do arithmetic to a greater precision then you should use something else.I usually prefer decimal for anything financial and do the rounding/truncation explicitly.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-06 : 06:29:29
|
| Actually MONEY data type provides you with precision that is just enough for the accounting figures (-922,337,203,685,477.5808 upto 922,337,203,685,477.5807 as per BOL) with the storage of 4 bytes, whereas, Decimal/Numeric data types can provide you with much more precision which is necessary in scientific/number-crunching apps.But I don't think SMALLMONEY is enough in its range for accounting figures (- 214,748.3648 through +214,748.3647 as per BOL).I will recommend using Money instead of SmallMoney, but ofcourse it depends on your app's requirements.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
fizzer666
Starting Member
6 Posts |
Posted - 2006-07-06 : 06:39:52
|
Thanks for the quick replies,Sorry but I'm still a bit confused on this. So nr prefers Decimal and harsh_athalye you prefer Money. Hmmm Ok, if I was to use Money instead of Smallmoney I would have a greater range of figures but would I still be able to easily do calculations like percentages? So would I be right in thinking that the only downside in using Decimal is that it uses more bytes. However, I could still keep accurate accounting figures using the decimal type. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-06 : 06:47:31
|
quote:
Originally posted by fizzer666
Thanks for the quick replies,Sorry but I'm still a bit confused on this. So nr prefers Decimal and harsh_athalye you prefer Money. HmmmOk, if I was to use Money instead of Smallmoney I would have a greater range of figures but would I still be able to easily do calculations like percentages? So would I be right in thinking that the only downside in using Decimal is that it uses more bytes. However, I could still keep accurate accounting figures using the decimal type.
On the broad level, yes. Using Decimal/Numeric data type for accounting figures is not a sin. And BTW, using Decimal datatype also u can consume less storage space by specifying precision you want. Lesser the precision, lesser will be the storage space required.And the datatype SmallMoney or Money depends on what data you are storing - if you are storing just percentages, then SmallMoney is fine but if storing big amounts, better go for Money.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-07-06 : 06:55:47
|
| Well, According to me i prefer the Decimal Datatype since form this you can get more accurate results, recently we have to change all our datatype from Money to Decimal due to accurracy issue. But it all depends on the Application and clients :-) ,if you wanna round upto 4 decimals then using Money will be fine but for more accurrcy then i guess Decimal is the good option. Chirag |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-06 : 06:57:55
|
| It depends what precision you want to work with.A lot of people just perform arithmetic an round/truncate the result which can cause problems with statements and such so I like to specify the precision at every stage so that there aren't rounding errors.Thought a money rounded diffiferently to a decimal but I can't find an example so maybe it doesn't anymore.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
fizzer666
Starting Member
6 Posts |
Posted - 2006-07-06 : 08:10:27
|
| Thanks for the replies everybody.In light of the replies and because of the problems i've already had with the money type i'm going for the decimal(9,2) option. Which I believe should be 5 bytes.Many thanks. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
When to use Money data type?