Friday, September 18, 2009

Money vs Decimal in Sql Server

I normally use the Money data type when designing databases , as I've always read that it was specifically designed for currencies. One of the advantages , for example , is that you can use a currency symbol with it. Recently I came across something very interesting while browsing the net. Apparently there are accuracy issues with the data type. I have below a simple example to demonstrate. Note that this isn't my example but something that I found on the web which I thought I'd share :

declare @m money
declare @d decimal(9,2)

set @m = 19.34
set @d = 19.34

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

So what would the results be ? Well if you're expecting 19.34 for the money variable , you'd be wrong !! You actually get 19.30. Yup, I was surprised as you are right now. I've even tested this in the Katmai CTP and it does the same thing. So from now on I'll try to use decimal and specify the precision I need , when creating tables.

No comments: