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

MoneyVsDecimal1

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?

  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.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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: