SQL: Datatype choice for currency (Money Vs Decimal)

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

 

Decimal type provides more precision to hold intermediate results to get the results you expect.

Disadvantages of money datatype:

  1. It is proprietary, so porting it is a pain. It is one of the many “Sybase Code Museum” features from decades ago. Remember the early versions of UNIX?
  2. Writing code in dialect when you don’t need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.
  3. It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.
  4. The MONEY data type has rounding errors.

 

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

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.

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

1
2
3
4
5
6
7
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.

Advertisements

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