What is the difference between Money and Decimal(19,4)

8

June 2, 2014 by Kenneth Fisher

One of my co-workers and I were discussing the difference between the data type MONEY and the data type DECIMAL(19,4). I was rather curious so I spent some time getting it all down on paper.

Details

First the obvious. MONEY takes up 8 bytes and stores the maximum possible values possible for the 8 bytes. 2^64 or -922,337,203,685,477.5808 to 922,337,203,685,477.5807. DECIMAL(19,4) will store any value that will fit into MONEY and then some, but it takes up 9 bytes. A DECIMAL(19,4) can hold a range of -999,999,999,999,999.9999 to 999,999,999,999,999.9999. This is actually considerably less than 2^72 (8bits * 9bytes) or -236,118,324,143,482,260.6848 to 236,118,324,143,482,260.6847 that actually could be stored in 9 bytes.
Next something interesting to try

```CREATE TABLE MoneyTest (
Mon1 money, Mon2 AS Mon1*Mon1, Mon3 AS Mon1*Mon1*Mon1,
Dec1 decimal(19,4), Dec2 AS Dec1*Dec1, Dec3 AS Dec1*Dec1*Dec1,
MonDec AS Mon1*Dec1, DecMon AS Dec1*Mon1);
GO
EXEC sp_help MoneyTest;
GO```

With an output of

If you look at the Length, Precision and Scale you will notice that they change for any of the computed columns that use the DECIMAL column. This is because if you (for example) multiply a DECIMAL(4,2) times another DECIMAL(4,2) you end up with a DECIMAL(8,4). It stops at Precision 38 because that is the maximum size of a DECIMAL data type. The MONEY data type however does not change when multiplied by itself.

Summary
Size Min Max Precision
Money 8 -922,337,203,685,477.5808 922,337,203,685,477.5807 Static
Decimal(19,4) 9 -999,999,999,999,999.9999 999,999,999,999,999.9999 Changes
SmallMoney 4 -214,748.3648 214,748.3647 Fixed
Decimal(10,4) 9 -999,999.9999 999,999.9999 Changes

So the major difference is that Money data types are smaller but have a fixed size. Decimal on the other hand takes up more space and when multiplying the size, precision, and scale are going to change. Personally that means that I’m going to story invoice amounts (for example) as Money (or even SmallMoney where I can) and tax rate as a Decimal.

8 thoughts on “What is the difference between Money and Decimal(19,4)”

1. Mike Bunyan says:

Seems to indicate multiplying MONEY against DECIMAL has consequences if not CAST correctly?

• Other than doing an implicit conversion you will end up with a similar result to DECIMAL * DECIMAL. The fixed precision will only happen with MONEY * MONEY.

2. unglesb says:

Money can also handle the dollar sign and commas, whereas decimal cannot. I know, weird, right?

SELECT CONVERT(MONEY,’\$1,000′) –works
SELECT CONVERT(DECIMAL,’1,000′) –doesn’t work
SELECT CONVERT(DECIMAL,’\$1000′) –doesn’t work

3. ---- says:

I think there is an important difference (in terms of speed) between MONEY/SMALLMONEY and DECIMAL: internally M/SM are BIGINT/INTegers. And DECIMAL has VARDECIMAL “compression” option. Aaron Bertrand have done some performance test with M and D.

4. philn5d says:

I actually came across this post due to Entity Framework 6.0 mapping the Money field to Decimal (19, 4). Not that this came up in production, but in testing I found that it becomes an issue when saving really large values.

So if you’re working with the US National Debt, you may not want to consider using Money as a data-type:

[ArgumentException: Parameter value ‘202020202020202000000.0000’ is out of range.]
System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam) +6999
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107

All called via SaveChanges() on the DbContext with no special update statements such as Stored Procedures or inline SQL.

It seems that it matters when working with really large money values, perhaps would encounter issues easily when working with Yen or a similar currency where the exchange rate is factors of a hundred.

• Good point. Of course with values that large hopefully you have done some careful planing of your database structure in general 🙂

This site uses Akismet to reduce spam. Learn how your comment data is processed.