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. I want to know 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

You should use decimal unless you are using MS Dynamics CRM. For some unknown reasons MS decided to make decimal range from -100_000_000_000 to 100_000_000_000. which isn't large enough for storing money.

The next thing that does not make sense is that within a CRM entity you can only have one money field in order to be able to correctly specify its currency.

Answer 2

Someone could make a case that you should store currency as MONEY, but CONVERT it to DECIMAL before doing calculations.

Calculation should not be totally dependent on storage type.

It is always good practice to explicitly convert your data to the desired type BEFORE using it in calculation, AND it saves a little storage.

To borrow from @SQLMenace's example:

DECLARE
--Just to drive the point, I'll use SMALLMONEY (4 bytes)
@mon1 SMALLMONEY,
@mon2 SMALLMONEY,
@mon3 SMALLMONEY,
@mon4 SMALLMONEY,
--This is the smallest DECIMAL that will hold this calculation (5 bytes)
@num1 DECIMAL(9,4),
@num2 DECIMAL(9,4),
@num3 DECIMAL(9,4),
@num4 DECIMAL(9,4)

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

--Convert it to decimal before calculation!
SET @mon4 = CONVERT(DECIMAL(19,4),@mon1)/
            CONVERT(DECIMAL(19,4),@mon2)*
            CONVERT(DECIMAL(19,4),@mon3)
SET @num4 = @num1/@num2*@num3
--Notice I didn't convert @mon4 from SMALLMONEY before presentation. 
--It is the appropriate data type for this presentation.

SELECT @mon4 AS moneyresult,
@num4 AS numericresult

Output:
2949.8525 2949.8525

The same!

Sure, it's not as clean, but you can take care of that with a little formatting.

To add to the point, try swapping the SELECT out for this:

SELECT
--I added a zero to @mon3 and @num3
@mon1 = 101.5, @mon2 = 339.253, @mon3 = 100000,
@num1 = 101.5, @num2 = 339.253, @num3 = 100000

Output:
Msg 8115, Level 16, State 8, Line 13
Arithmetic overflow error converting int to data type numeric.

DECIMAL(9,4) cannot handle this number size, but SMALLMONEY can.

DECIMAL(10,4) (9 bytes) is the actual equivalent to SMALLMONEY (4 bytes).

To agree with @GerardONeill, in most circumstances you are not saving much space, but I do see that a case could be made that SMALLMONEY (4 bytes) being one byte less than DECIMAL(9,4) (5 bytes) when scaled up to a trillion values saves you a terabyte. (Where would you have a trillion records in small values? Did I hear someone say "microtransactions?") And furthermore, DECIMAL(9,4) does not store as many numbers! DECIMAL(10,4) jumps up to 9 bytes, and scaled up to a trillion values SMALLMONEY saves you 5 terabytes.

NOTE: If you compare MONEY (8 bytes) with DECIMAL(19,4) (9 bytes). One byte less saves a terabyte once you reach a trillion, but in this case DECIMAL(19,4) gives you two more zeros if you add them to @mon3 and @num3 in the SELECT above. So it all really depends on how much you need to store.

Storage is cheep nowadays, so I'm not saying it's a strong case, but one could make the case and it is worth noting.

To summarize the value differences:

To REPLACE `SMALLMONEY (4 bytes) you would have to choose between

1. DECIMAL(9,4) (5 bytes) and sacrifice range of values OR
2. DECIMAL(10,4) (9 bytes) for the same range but at more than double the storage used.

To summarize the argument, here are a few principles to follow:

1. MONEY is for presentation, not calculation
2. DECIMAL(n,n) should be used for precise calculations
3. CONVERT your data before calculating with it.

That last one may just be a programmer thing, but being deliberate with your data types at the point of use (NOT JUST the point of storage) is important.

For instance, let's mess with @SQLMenace's answer again:

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

What data types are being used in those calculations?

I HAVE NO IDEA

...except he did imply they were all DECIMAL

...BUT WERE THEY???

You can probably see where I'm going with this. From the code maintenance perspective, I personally prefer to have everything I need to know about the data I'm working with directly in front of me.

AND THEN

If you're already going to be converting the data into the type you need for calculation, why store it in a larger type?

Anyway, food for thought.

Answer 3

All the previous posts bring valid points, but some don't answer the question precisely.

The question is: Why would someone prefer money when we already know it is a less precise data type and can cause errors if used in complex calculations?

You use money when you won't make complex calculations and can trade this precision for other needs.

For example, when you don't have to make those calculations, and need to import data from valid currency text strings. This automatic conversion works only with MONEY data type:

SELECT CONVERT(MONEY, '$1,000.68')

I know you can make your own import routine. But sometimes you don't want to recreate a import routine with worldwide specific locale formats.

Another example, when you don't have to make those calculations (you need just to store a value) and need to save 1 byte (money takes 8 bytes and decimal(19,4) takes 9 bytes). In some applications (fast CPU, big RAM, slow IO), like just reading huge amount of data, this can be faster too.

Answer 4

I found a reason about using decimal over money in accuracy subject.

DECLARE @dOne   DECIMAL(19,4),
        @dThree DECIMAL(19,4),
        @mOne   MONEY,
        @mThree MONEY,
        @fOne   FLOAT,
        @fThree FLOAT

 SELECT @dOne   = 1,
        @dThree = 3,    
        @mOne   = 1,
        @mThree = 3,    
        @fOne   = 1,
        @fThree = 3

 SELECT (@dOne/@dThree)*@dThree AS DecimalResult,
        (@mOne/@mThree)*@mThree AS MoneyResult,
        (@fOne/@fThree)*@fThree AS FloatResult

---

DecimalResult > 1.000000

MoneyResult > 0.9999

FloatResult > 1

Just test it and make your decision.

Answer 5

I want to give a different view of MONEY vs. NUMERICAL, largely based my own expertise and experience... My point of view here is MONEY, because I have worked with it for a considerable long time and never really used NUMERICAL much...

MONEY Pro:

• Native Data Type. It uses a native data type (integer) as the same as a CPU register (32 or 64 bit), so the calculation doesn't need unnecessary overhead so it's smaller and faster... MONEY needs 8 bytes and NUMERICAL(19, 4) needs 9 bytes (12.5% bigger)...

  MONEY is faster as long as it is used for it was meant to be (as money). How fast? My simple SUM test on 1 million data shows that MONEY is 275 ms and NUMERIC 517 ms... That is almost twice as fast... Why SUM test? See next Pro point

• Best for Money. MONEY is best for storing money and do operations, for example, in accounting. A single report can run millions of additions (SUM) and a few multiplications after the SUM operation is done. For very big accounting applications it is almost twice as fast, and it is extremely significant...
• Low Precision of Money. Money in real life doesn't need to be very precise. I mean, many people may care about 1 cent USD, but how about 0.01 cent USD? In fact, in my country, banks no longer care about cents (digit after decimal comma); I don't know about US bank or other country...

MONEY Con:

• Limited Precision. MONEY only has four digits (after the comma) precision, so it has to be converted before doing operations such as division... But then again money doesn't need to be so precise and is meant to be used as money, not just a number...

But... Big, but here is even your application involved real-money, but do not use it in lots of SUM operations, like in accounting. If you use lots of divisions and multiplications instead then you should not use MONEY...

Answer 6

I just saw this blog entry: Money vs. Decimal in SQL Server.

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 the money type, you will get 19.30 instead of 19.34. I am not sure if there is an application scenario that divides money into 1000 parts for calculation, but this example does expose some limitations.

Answer 7

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 you don't divide money by money:

Here is one of my queries to calculate correlations, and 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 8

You shouldn't use money when you need to do multiplications / divisions on the value. Money is stored in the same way an integer is stored, whereas decimal is stored as a decimal point and decimal digits. This means that money will drop accuracy in most cases, while decimal will only do so when converted back to its original scale. Money is fixed point, so its scale doesn't change during calculations. However because it is fixed point when it gets printed as a decimal string (as opposed to as a fixed position in a base 2 string), values up to the scale of 4 are represented exactly. So for addition and subtraction, money is fine.

A decimal is represented in base 10 internally, and thus the position of the decimal point is also based on the base 10 number. Which makes its fractional part represent its value exactly, just like with money. The difference is that intermediate values of decimal can maintain precision up to 38 digits.

With a floating point number, the value is stored in binary as if it were an integer, and the decimal (or binary, ahem) point's position is relative to the bits representing the number. Because it is a binary decimal point, base 10 numbers lose precision right after the decimal point. 1/5th, or 0.2, cannot be represented precisely in this way. Neither money nor decimal suffer from this limitation.

It is easy enough to convert money to decimal, perform the calculations, and then store the resulting value back into a money field or variable.

From my POV, I want stuff that happens to numbers to just happen without having to give too much thought to them. If all calculations are going to get converted to decimal, then to me I'd just want to use decimal. I'd save the money field for display purposes.

Size-wise I don't see enough of a difference to change my mind. Money takes 4 - 8 bytes, whereas decimal can be 5, 9, 13, and 17. The 9 bytes can cover the entire range that the 8 bytes of money can. Index-wise (comparing and searching should be comparable).

Answer 9

As a counter point to the general thrust of the other answers. See The Many Benefits of Money…Data Type! in SQLCAT's Guide to Relational Engine

Specifically I would point out the following

Working on customer implementations, we found some interesting performance numbers concerning the money data type. For example, when Analysis Services was set to the currency data type (from double) to match the SQL Server money data type, there was a 13% improvement in processing speed (rows/sec). To get faster performance within SQL Server Integration Services (SSIS) to load 1.18 TB in under thirty minutes, as noted in SSIS 2008 - world record ETL performance, it was observed that changing the four decimal(9,2) columns with a size of 5 bytes in the TPC-H LINEITEM table to money (8 bytes) improved bulk inserting speed by 20% ... The reason for the performance improvement is because of SQL Server’s Tabular Data Stream (TDS) protocol, which has the key design principle to transfer data in compact binary form and as close as possible to the internal storage format of SQL Server. Empirically, this was observed during the SSIS 2008 - world record ETL performance test using Kernrate; the protocol dropped significantly when the data type was switched to money from decimal. This makes the transfer of data as efficient as possible. A complex data type needs additional parsing and CPU cycles to handle than a fixed-width type.

So the answer to the question is "it depends". You need to be more careful with certain arithmetical operations to preserve precision but you may find that performance considerations make this worthwhile.

Answer 10

Everything is dangerous if you don't know what you are doing

Even high-precision decimal types can't save the day:

declare @num1 numeric(38,22)
declare @num2 numeric(38,22)
set @num1 = .0000006
set @num2 = 1.0
select @num1 * @num2 * 1000000

1.000000 <- Should be 0.6000000

---

The money types are integers

The text representations of smallmoney and decimal(10,4) may look alike, but that doesn't make them interchangeable. Do you cringe when you see dates stored as varchar(10)? This is the same thing.

Behind the scenes, money/smallmoney are just a bigint/int The decimal point in the text representation of money is visual fluff, just like the dashes in a yyyy-mm-dd date. SQL doesn't actually store those internally.

Regarding decimal vs money, pick whatever is appropriate for your needs. The money types exist because storing accounting values as integer multiples of 1/10000th of unit is very common. Also, if you are dealing with actual money and calculations beyond simple addition and subtraction, you shouldn't be doing that at the database level! Do it at the application level with a library that supports Banker's Rounding (IEEE 754)

Answer 11

Well, I like MONEY! It's a byte cheaper than DECIMAL, and the computations perform quicker because (under the covers) addition and subtraction operations are essentially integer operations. @SQLMenace's example—which is a great warning for the unaware—could equally be applied to INTegers, where the result would be zero. But that's no reason not to use integers—where appropriate.

So, it's perfectly 'safe' and appropriate to use MONEY when what you are dealing with is MONEY and use it according to mathematical rules that it follows (same as INTeger).

Would it have been better if SQL Server promoted division and multiplication of MONEY's into DECIMALs (or FLOATs?)—possibly, but they didn't choose to do this; nor did they choose to promote INTegers to FLOATs when dividing them.

MONEY has no precision issue; that DECIMALs get to have a larger intermediate type used during calculations is just a 'feature' of using that type (and I'm not actually sure how far that 'feature' extends).

To answer the specific question, a "compelling reason"? Well, if you want absolute maximum performance in a SUM(x) where x could be either DECIMAL or MONEY, then MONEY will have an edge.

Also, don't forget it's smaller cousin, SMALLMONEY—just 4 bytes, but it does max out at 214,748.3647 - which is pretty small for money—and so is not often a good fit.

To prove the point around using larger intermediate types, if you assign the intermediate explicitly to a variable, DECIMAL suffers the same problem:

declare @a decimal(19,4)
declare @b decimal(19,4)
declare @c decimal(19,4)
declare @d decimal(19,4)

select @a = 100, @b = 339, @c = 10000

set @d = @a/@b

set @d = @d*@c

select @d

Produces 2950.0000 (okay, so at least DECIMAL rounded rather than MONEY truncated—same as an integer would.)

Answer 12

I realise that WayneM has stated he knows that money is specific to SQL Server. However, he is asking if there are any reasons to use money over decimal or vice versa and I think one obvious reason still ought to be stated and that is using decimal means it's one less thing to worry about if you ever have to change your DBMS - which can happen.

Make your systems as flexible as possible!

Answer 13

We've just come across a very similar issue and I'm now very much a +1 for never using Money except in top level presentation. We have multiple tables (effectively a sales voucher and sales invoice) each of which contains one or more Money fields for historical reasons, and we need to perform a pro-rata calculation to work out how much of the total invoice Tax is relevant to each line on the sales voucher. Our calculation is

vat proportion = total invoice vat x (voucher line value / total invoice value)

This results in a real world money / money calculation which causes scale errors on the division part, which then multiplies up into an incorrect vat proportion. When these values are subsequently added, we end up with a sum of the vat proportions which do not add up to the total invoice value. Had either of the values in the brackets been a decimal (I'm about to cast one of them as such) the vat proportion would be correct.

When the brackets weren't there originally this used to work, I guess because of the larger values involved, it was effectively simulating a higher scale. We added the brackets because it was doing the multiplication first, which was in some rare cases blowing the precision available for the calculation, but this has now caused this much more common error.

Answer 14

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.