Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?

Question

I'm curious as to whether or not there is a real difference between the money datatype and something like decimal(19,4) (which is what money uses internally, I believe).

I'm aware that money is specific to SQL Server. What I want to know is if there is a compelling reason to choose one over the other; most SQL Server samples (e.g. the AdventureWorks database) use money and not decimal for things like price information.

Should I just continue to use the money datatype, or is there a benefit to using decimal instead? Money is fewer characters to type but that's not a valid reason :)

Answer 1

Never ever should you use money it is not precise and it is pure garbage, always use decimal/numeric

run this to see what I mean

DECLARE
    @mon1 MONEY,
    @mon2 MONEY,
    @mon3 MONEY,
    @mon4 MONEY,
    @num1 DECIMAL(19,4),
    @num2 DECIMAL(19,4),
    @num3 DECIMAL(19,4),
    @num4 DECIMAL(19,4)

    SELECT
    @mon1 = 100, @mon2 = 339, @mon3 = 10000,
    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3
    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,
    @num4 AS numericresult

Output: 2949.0000 2949.8525

To some of the people who said that toy don't divide money by money

Here is one of my queries to calculate correlations, changing that to money gives wrong results

select t1.index_id,t2.index_id,(avg(t1.monret*t2.monret) 
    -(avg(t1.monret) * avg(t2.monret)))
            /((sqrt(avg(square(t1.monret)) - square(avg(t1.monret)))) 
            *(sqrt(avg(square(t2.monret)) - square(avg(t2.monret))))),
current_timestamp,@MaxDate
            from Table1 t1  join Table1 t2  on t1.Date = traDate
            group by t1.index_id,t2.index_id

Answer 2

SQLMenace said money is inexact. But you don't multiply/divide money by money! How much is 3 dollars times 50 cents? 150 dollarcents? You multiply/divide money by scalars, which should be decimal.

DECLARE
@mon1 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@num2*@num3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult

Results in the correct result:

moneyresult           numericresult
--------------------- ---------------------------------------
2949.8525             2949.8525

money is good as long as you don't need more than 4 decimal digits, and you make sure your scalars - which do not represent money - are decimals.

Answer 3

I just saw this entry... http://thavash.spaces.live.com/blog/cns!CF6232111374DFD2!223.entry

which basically says that money has a precision issue....

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

set @m = 19.34
set @d = 19.34

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

For money type, you will get 19.30 instead of 19.34. I am not sure if there is application scenario that divides money into 1000 parts for calculation but this example does expose some limitations.

Harsha

Answer 4

@configurator you may be right when you said

you don't multiply/divide money by money!

but as per Data Type Precedence standards when an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

so in your example when you divided money with decimal, money was implicitly converted to decimal as decimal has higher precedence than money and thus you had same result for decimal/decimal and money/decimal calculations.

Answer 5

You have to be careful when sum/multiple/divide/subtract variable that are of different types as SQL automatically cast the result to one of the two types. It's a good practice to operate on variable of the same type, or always explicitly cast the result (or even each variable within the operation/expression).

Answer 6

This works: ROUND(yourmoneyvalue, 2) and if you need to do it in the code Math.Round(yourmoneyvalue, 2).