What is the difference between Money and Decimal(19,4)
8June 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.
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.
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
Excellent point. I hadn’t thought about that.
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.
Thanks for bringing it up. I personally have no preference for one or the other but use what seems most appropriate at the time. It’s just important to know as much about the differences as possible so you can decide what is/is not appropriate. I found Aaron’s post very interesting and thanks for bringing it up. Here is a link for anyone interested. http://sqlblog.com/blogs/aaron_bertrand/archive/2008/04/27/performance-storage-comparisons-money-vs-decimal.aspx
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.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +6741487
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +586
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
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 🙂