SQL Money Data Type - Should I Use MONEY or DECIMAL?


Share this post:
sql money
That's a lot of money

SQL MONEY Data Type: What Is It? Should You Use It?

by Jeremy Hillpot

The MONEY data type in SQL Server gets a bad rap. Ask a few people whether to use it and they'll probably say:

  • "The MONEY data type is bad and should never be used."
  • "The MONEY data type could result in unintentional loss of precision from rounding errors. It's better to use the DECIMAL data type."

Given these general opinions, it's tempting to ditch the MONEY data type completely and use the DECIMAL data type instead. That's decent advice, since the DECIMAL data type doesn't come with data inaccuracy problems, and the DECIMAL data type can do everything the MONEY data type can. However, there are still some instances when MONEY is useful and appropriate.

MONEY vs. DECIMAL: Background and History

In the earlier days of SQL Server, the DECIMAL data type offered flexibility because you could use it to save most numbers. However, DECIMAL required a lot of storage space (17 bytes). With the MONEY data type (a.k.a, NUMERIC), the number of digits following the decimal place was limited to four – e.g., 12345.6789 – so the storage requirement was less (8 bytes). Therefore, MONEY was useful when you wanted to save on storage.

MONEY will still save space if you're not using SQL Server Enterprise Edition. If you're using enterprise edition, on the other hand, subsequent updates to SQL Server means that this convention doesn't apply to you.

SQL Server 2005, Service Pack 2:

The release of Service Pack 2 SQL Server 2005 introduced vardecimal storage for the DECIMAL data type. This changed the storage space needed for DECIMAL, causing it to vary based on the number of digits. As a result, the DECIMAL data type could require 5, 9, 13, or 17 bytes for storage – depending on the size of the figure you're storing.

By using vardecimal to limit a figure to 4 digits after the decimal – just like MONEY is limited to 4 – users could reduce the storage requirement for the DECIMAL data type down to 5 bytes for numbers up to 429496.7296. For larger numbers, the storage requirement went up – but if your numbers fell within this threshold of 429496.7296, you could save a lot of space with DECIMAL and vardecimal. Moreover, by limiting yourself to two digits after the decimal, you could save numbers up to 42949672.96 with 5 bytes of space.

SQL Server 2018:

With SQL Server 2018, the use of row compression became standard with DECIMAL and it replaced the use of vardecimal. Row compression also allows you to save DECIMAL numbers with less space than MONEY, and it rendered the space-saving features of vardecimal moot.

Ultimately, if you're not a user of SQL Server Enterprise Edition, neither of these updates apply to you. The MONEY datatype still saves on storage space due to its 8-byte storage requirement versus DECIMAL's 17-byte requirement.

Does this mean that non-enterprise users should still choose MONEY to save on storage space? The answer to this question depends on the type of transactions you will perform on the data, and how important data accuracy is to you.

MONEY Data Type and the Unintentional Loss of Precision

Saving storage space is important – especially with massive SQL databases – but the accuracy of your data is a lot more important. If you're planning to use the MONEY data type to save space with a non-enterprise edition of SQL Server, remember that multiplication and division calculations with the MONEY data type can cause rounding errors that result in the unintentional loss of precision.

Here's why multiplication and division is a problem with MONEY:

  • MONEY only saves numerical information up to the 4th decimal place.
  • If your multiplication or division calculations result in an integer that goes to the 5th decimal place or more, MONEY will round it off, causing an accuracy error.
  • While individual calculations might not be off by a large degree, the more calculations you perform, the more inaccurate the results become, creating the potential for a snowball of inaccurate information.

Addition and subtraction calculations aren't a problem with MONEY, so if that's all you need – and you're a non-enterprise user of SQL Server – the MONEY data will help you save space. Otherwise, use DECIMAL instead of MONEY. DECIMAL doesn't cut numbers short with a rounding error, so you're multiplying and dividing the whole number, and thereby maintaining the accuracy of your calculations.

Imagine You're Tracking Bank Information Related to Check Deposits or Unclaimed Property Records:

As an example of how important this is, imagine you're a service that tracks data and records related to your client's check deposits, like CheckDeposit.io, which manages customer check deposit records for any bank (e.g. Wells Fargo, Bank of America) by storing every deposit slip, check image, and remittance image. It's a simple, straightforward record-keeping solution that also provides complex calculations, insights, and analytics on those records, so data accuracy is paramount.

Although CheckDeposit.io is dealing with records related to "money," the MONEY data type isn't going to work because – as soon as they apply a multiplication or division calculation to the data – it corrupts the results.

checks
Dollars and cents

Conversely, imagine you're dealing with millions of unclaimed property records, like the millions of California Unclaimed Property records that make up the $10 billion California holds in unclaimed funds, or the millions of New York Unclaimed Funds records that make up New York's $16 billion in unclaimed property.  In this case you could use the money data type, since you would not be multiplying or dividing the numbers, and accuracy does not matter as much (since the use is mainly for display).  The smaller storage requirements of the money data type would help when dealing with tens of millions of records.

Should I Use the MONEY data type or DECIMAL data type?

Considering everything we just discussed, here's when you should use the MONEY data type vs. DECIMAL data type in SQL Server:

  • Are you a non-enterprise user of SQL Server, don't plan to perform multiplication and division calculations, and you need to save space? The MONEY data type will cut down your storage requirements. Also, if none of your figures exceed 214,748.3647, the SMALLMONEY data type will save even more space.
  • Are you a non-enterprise user of SQL and need to perform multiplication and division calculations  or simply aren't concerned about saving storage space? Avoid the threat of rounding errors and the unintentional loss of precision by using the DECIMAL data type.
  • Are you using SQL Server Enterprise Edition? There's no reason to use the MONEY data type if you plan to use SQL Server Enterprise Edition. Maintain the accuracy of your calculations with the DECIMAL data type and save space with the row compression feature introduced in SQL Server 2008.

SQLBot: A Lightweight Solution to Automate Your SQL Queries

By now, you should have a thorough understanding of when to use MONEY vs. DECIMAL for the most accurate database records. Of course, this is SQL 101 on our end, since SQLBot is in the business helping you manage, query, and share your database information more efficiently.

As a simple, lightweight service that lets you schedule SQL queries in advance, SQLBot pushes query results to your team through Slack and email on autopilot. If you have a query that you run on a daily, weekly, or monthly basis – and you have to share the results with your team – SQLBot automates the process so you can focus on other tasks.

Set it up, forget about it.

Get the metrics you need, on time, every time. Try SQLBot now. It's free!

*Photo credit: Photo by Nina P on Reshot