What data-type should I use to store monetary values?

Question

I've read I should use money, but in todays fast paced world maybe that's obsolete now.

What should I use?

Answer 1

Papuccino,

I don't recommend the types money and smallmoney unless you are certain the only arithmetic you plan to do is addition and subtraction. If you might be dealing with exchange rates, percentages, and so on, you risk real problems with these types.

Here's just one small example to show you a difference between using money, decimal, and float when division is involved. It's possible to come up with examples where the difference is much more dramatic.

declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
declare @f1 float, @f2 float, @f3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @m3 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @d3 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @f3 = @f1/@f2;
select @m3, @d3, @f3;

Result: 0.0028 0.0029 0.00289855072463768

Depending on the industry, there may be guidelines or regulations to help you decide on the right data type. There is no one right answer.

Added remarks:

You are correct that money/money should not be money, but SQL Server (inexplicably) produces exactly that result: type money from the quotient of two money values. This is bogus, but as you see from the example below, it is what you get, even though it makes no sense:

declare @m1 money, @m2 money;
declare @d1 decimal(19,4), @d2 decimal(19,4);
set @m1 = 1.00;
set @m2 = 345.00;
set @d1 = 1.00;
set @d2 = 345.00;
select @m1/@m2, @d1/@d2

Result: 0.0028 0.0028985507246376811

The result with type money, 0.0028, is 3-4% less than the correct result.

Of course, there are many situations where you need to divide currency values. The danger of using the money type is that the quotient is the wrong type (and an answer not close enough to the correct one). Examples of questions that require dividing currency:

Suppose you exchange 320 Yuan and the bank gives you 47.3 US dollars. What is the exchange rate you've been given?

Suppose you invest $23 and a year later it's worth $31. What is your percent rate of return?

Both of these calculations require dividing currency values.

Answer 2

No, money should still work.

Answer 3

Why should money be obsolete? It goes up to over 900 trillions, hundreds of times the Federal Government's budget -- what kind of money amounts do you possibly need to store?-) (I guess that maybe Zimbabwe dollars might eventually have been a problem, but they kept resetting it by multiples of billions and trillions, and last April it was finally suspended; they use US dollars or other foreign currencies now for payments & accounting in Zimbabwe).

Answer 4

I agree money divided by money is bogus. But money divided by days is real. If you are dividing small amounts of money by the number of days you need to spead the cost, it is most important to keep watch of this phenomenon. I cast/convert money to float, do computation before eventually storing the end result to money data type. Hope this helps.

Answer 5

It surprises me that nobody mentioned it before.

money is 8 bytes. decimal is 5, 9, 13 or 17 bytes depending on precision (precision is not the number of decimal digits after the decimal point, it is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point). So, to mimic range of values that money supports (-922,337,203,685,477.5808 to 922,337,203,685,477.5807) you need decimal(19,4).

+-------------------+---------------+
| decimal precision | Storage bytes |
+-------------------+---------------+
| 1 - 9             |             5 |
| 10-19             |             9 |
| 20-28             |            13 |
| 29-38             |            17 |
+-------------------+---------------+

If storing 9 bytes instead of 8 doesn't seem like a big deal, you should keep in mind that money is a native processor type, like 64-bit bigint, but decimal is not. It means that summing up billions of money values would be faster than summing up decimal values. Performing other calculations like division would have even larger difference.

On my virtual machine with SQL Server 2014 Express I ran this simple test. dbo.Numbers is a table with 10,000 rows with one int column Number with values from 1 to 10,000.

CREATE TABLE [dbo].[Numbers](
    [Number] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED 
(
    [Number] ASC
))

I ran this in SQL Sentry Plan Explorer:

DECLARE @VarM money = 1234.5678;

SELECT
    AVG(@VarM / N1.Number)
FROM 
    dbo.Numbers AS N1
    CROSS JOIN dbo.Numbers AS N2
;


DECLARE @VarD decimal(19,4) = 1234.5678;

SELECT
    AVG(@VarD / N1.Number)
FROM 
    dbo.Numbers AS N1
    CROSS JOIN dbo.Numbers AS N2
;

Execution plan is the same for both queries (well, there are different implicit conversions of Number: to money and decimal), but run time is 15 seconds vs. 40 seconds. It is quite noticeable for me.

---

Of course, you need to know that money has only 4 decimal places. If you perform calculations you need to be aware of the types (and their precedence, i.e. what is implicitly converted into what) and make sure that intermediate results are of the appropriate type by casting operands into proper types if needed. This warning applies to calculations with any types, not just money. When you divide two int values you should know that result is int and not be surprised that 4 / 5 = 0.