What is the difference between Decimal (19, 4) and Money Data type in SQL Server

Today, one of our developers requested that we use “Money” as a data type for a currency column. I suggested that, instead of Money, we should consider using DECIMAL (19, 4).  MONEY has essentially the same definition, but it needs to be used with caution.  Here’s more about why that is:

  • MONEY takes up 8 bytes. 922,337,203,685,477.5808 to 922,337,203,685,477.5807.
  • DECIMAL(19,4) takes up 9 bytes. -999,999,999,999,999.9999 to 999,999,999,999,999.9999.
  • MONEY can handle the dollar sign and commas, whereas decimal cannot.
  • DECIMAL(19,4) can be defined with precision & scale.
  • MONEY data type has rounding error if the fractional units bigger than 5 decimal places. Also if you are going to use MONEY data in a calculation involving multiplication or division, it is cautious to cast or round to the DECIMAL datatype.

Let’s look at an example;

BEGIN
DECLARE
@amt_1 MONEY,
@amt_2 MONEY,
@amt_3 MONEY,
@money_out MONEY,
@decimal_out decimal(19,4),
@cast_decimal_out decimal(19,4);

SET @amt_1 = 789456.12;
SET @amt_2 = 7894.56;
SET @amt_3 = 1234.56

SET @money_out = (@amt_3 / @amt_2) * @amt_1;
SET @decimal_out = (@amt_3 / @amt_2) * @amt_1;
SET @cast_decimal_out = (CAST(@amt_3 AS decimal(19,4))    / CAST( @amt_2 AS decimal(19,4))) * CAST( @amt_1 AS decimal(19,4)); 

SELECT @money_out as money_out, @decimal_out as decimal_out, @cast_decimal_out as cast_decimal_out;
END;

Decimal or Money

Here is another example of the Money precision issue;;

DECLARE @decimal_1 DECIMAL(19,4),
@decimal_2 DECIMAL(19,4),
@money_1 MONEY,
@money_2 MONEY,
@float_1 FLOAT,
@float_2 FLOAT;

SELECT @decimal_1 = 1,
@decimal_2 = 3,
@money_1 = 1,
@money_2 = 3,
@float_1 = 1,
@float_2 = 3;

SELECT (@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult,
(@money_1/@money_2)*@money_2 AS MoneyResult,
(@float_1/@float_2)*@float_2 AS FloatResult;

Decimal or Money

 

Bottom line, the decision to use the money or decimal(19,4) data type is dependent on how you plan to use the data… If you’re not going to do anything to the values other than add or subtract, either Money or Decimal(19,4) will work just fine.  But if you’re going to do any calculations like multiply or divide, you’ll need at least 4 decimal places to do the calculations without losing overall accuracy.  Even though the MONEY datatype in SQL server can save substantial space when compared with DECIMAL(19,4) datatype, there is still no good reason to use the MONEY datatype in SQL server for a T-SQL variable in a stored procedure, batch, or function.

MONEY more accurately represents the real world situation, where each value is rounded to the nearest cent as calculated, then the average is again rounded. In a long calculation chain, the difference can wind up being considerably large than one cent.

 

Scroll to top