SQL Server Performance Forum – Threads Archive

Money Vs Decimal Data Type

1) Can anybody know what is the different of using Money and Decimal(17,2) data type in BDS 2006? 2) Which one is better to use? and why? 3) In terms of performance, which one is preferred? 4) What about database storage, which one consume less storage space? Need your help urgently. Best Regards,
sm

---

you have money (8byte) and small money (4byte) money is essential an integer,
except instead of counting 0, 1, 2
money counts 0, 0.0001, 0.0002 so money behaves as integer in terms of performance and overhead decimal is 5, 9, 13, etc bytes
there is a lot of cpu overhead to handle a decimal if the fixed 4 decimal place precision works for you
use it over decimal even if it is not really money if you are working with real money,
you will want to put the following code into your money transactions: BEGIN TRAN
amount = amount – 1
send 1 to joe
COMMINT TRAN

---

Rumours are that MONEY will be deprecated in a future version, so I would stick with DECIMAL. SP2 introduces the new VARDECIMAL data type, which resolves some of the storage overhead issues. But apart from storage, the main reason to use DECIMAL over MONEY is precision as Joe indicated. Consider this small example (by Steve Kass):
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/@m2
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3 ——————— —————————————
0,0028 0.0029 (1 row(s) affected) According to their data type specifications both results are "correct". Now, calculate this yourself in Excel and see what result you get and what would be appropriate for your situation.
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

]]>