18 April 2018

7 Comments

18 April 2018

7 Comments

Avoid use of the MONEY and SMALLMONEY datatypes (BP022)

Use of the MONEY and SMALLMONEY datatypes can lead to unintentional loss of precision, due to rounding errors, during calculations. It is generally far better to use the DECIMAL, a.k.a. NUMERIC, type.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify monetary values preceded by a currency symbol, but SQL Server doesn’t store any currency information at all with the actual numeric values, so the purpose of this is unclear.

It has limited precision; the underlying type is a BIGINT or, in the case of SMALLMONEY, an INT, so you can unintentionally get a loss of precision due to rounding errors. While simple addition or subtraction is fine, more complicated calculations that can be done for financial reports can show errors. Although the MONEY datatype generally takes less storage, and takes less bandwidth when sent over networks, via TDS, it is generally far better to use a data type such as the DECIMAL or NUMERIC type, which is less likely to suffer from rounding errors or scale overflow.

A recommendation to avoid use of the MONEY or SMALLMONEY datatypes is included as a “Best Practice” code analysis rule in SQL Prompt (BP022).

Rounding errors when using MONEY datatype

The MONEY and SMALLMONEY data types are accurate to roughly a ten-thousandth of the monetary units that they represent. SMALLMONEY is accurate between – 214,748.3648 and 214,748.3647 whereas MONEY is accurate between -922,337,203,685,477.5808 (-922,337 trillion) and 922,337,203,685,477.5807 (922,337 trillion).

Although MONEY can be represented with a currency symbol, this information isn’t stored. Under the covers, MONEY is stored as an integer data type. A decimal number, the more usual choice for storing a monetary value, can range accurately between -10^38 +1 through 10^38 – 1. Several sqillion!

The scientific world can tolerate tiny rounding errors and margins of error, but in finance a monetary calculation is either right or wrong. It is futile to argue that the odd cent or pence isn’t worth worrying about; I have, myself, been laughed at when I was a smidgen out from the right answer.

Take the calculation in Listing 1, which is the simplest I can think of that illustrates the problem.

Listing 1

Here are the results:

Notice the lack of any currency symbols for the Portion and Total values. The currency isn’t stored. It was useful in the VALUES clause because it indicated to SQL Server that it should parse the scalar literal values such as $124.33 into the MONEY datatype. Aside from that, though, are the percentage values correct? Let’s check that in Excel:

Hmm: doesn’t look quite right. Let’s rerun the calculation with decimal arithmetic (you’ll need to round the total or cast to numeric with a scale of two).

Listing 2

This time, the answers are the same as we saw in Excel:

Incidentally, if you rerun Listing 2 but with the currency symbol in front of each of the values we’re inserting for Total and Portion, then you’ll still get the correct percentage values (under the covers, SQL Server implicitly converts the monetary values to DECIMAL (19,4) before inserting them into the table variable).

The following figure shows the results of doing the reverse calculations i.e. calculating the portion values, from the total and percentage. The fact that we cannot calculate the portion (or total) exactly, from the percentage values produced using the MONEY datatype (Listing 1), confirms that there are rounding errors in those percentage values.

Other errors when using MONEY

You can also get scale overflow errors if you try to calculate correlations the classical way, when using MONEY values. The values stored in the intermediate sum of the squares calculations can get enormous, if you are trying to find relationships between monetary values and other variables over many rows. This will cause errors in the value of the correlation. If you cannot avoid the MONEY datatype then it is far better to use the built-in StDevP() aggregate function to get the correlation.

Summary

Basically, it pays to do calculations in DECIMAL (a.k.a. NUMERIC) with as many digits to the right of the decimal point as practical, and only using two or three decimal places to display the result. A scale of four digits to the right of the decimal point isn’t always sufficient for a datatype that is involved in any operations beyond addition or subtraction. Be aware also of ‘Bankers rounding’ in calculations.

MONEY can be made to perform well and accurately if you know all the constraints and workarounds, such as using the NUMERIC datatype within calculations using division or multiplication, or employing the built-in aggregate functions. MONEY uses integers under the covers, so it is fast, and will generally use less storage, and is particularly suited to being transmitted across a network as TDS. However, it is for experts only.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Prompt Tip: how to control when the suggestion box pops up

    By default, SQL Prompt shows code auto-completion suggestions automatically, and continuously. Phil Factor shows how to control this behavior, for the times when you need it to be a little less intrusive, such as when working through more intricate coding problems that require careful thought.

  • Article

    Driving up database coding standards using SQL Prompt

    A strategic view of how a development team can use SQL Prompt to establish and share coding standards, through code analysis rules, formatting styles and code snippets.

  • Article

    How to Test SQL Server Functions and Procedures using SQL Prompt

    Phil Factor shows how to use the "Script As Insert" feature in SQL Prompt to refactor a table of "input and expected output" values into a simple test harness for stored procedures and functions.

  • Article

    SQL Prompt Safety Net Features for Developers

    Ever accidentally executed code while connected to the right database but the wrong server? Phil Factor describes a few common mishaps that everyone working in SSMS will have experienced, and how SQL Prompt "Tab Magic" provides insurance against their consequences.

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly

  • jeff webb

    Can you please explain in more detail what you mean when you state that money is saved as a BIGINT or INT? Those data types don’t hold decimal values so I don’t understand how that would work. I thought I read somewhere that MONEY was stored as decimal with 4 positions to the right of the decimal point.
    Thank you

    • Andrew Chegodaev

      Money is a fixed point number based on Bigint type. All decimal values get multiplied by 10000 and rounded to the integer. E.g. when you want to store $1.23 you are actually store an integer value of 12300. When you do the math the value is transparently divided by 10000 to get the initial decimal (where necessary)

      Declare @val money = $1.23
      Select cast(@val as binary(8))

  • Andrew Chegodaev

    0) There is no such a coin as 0.0025cents or $0.000025. Please do not mix money and decimal numbers.
    1) There is an approximately 2.5 times performance gain when MONEY type is in use.
    2) Please use the tools appropriately. The precision loss occurs when you round the value between the operations, just like
    select CAST(199.50/271.00 AS DECIMAL(10,2))*100
    For the example above the issue disappears when you put multiplication before the division:
    SELECT
    CAST(199.50 AS MONEY)/CAST(271.00 AS MONEY)*100 as Loss, — this is per example
    CAST(199.50 AS MONEY)*100/CAST(271.00 AS MONEY) as NoLoss — this is how to avoid the issue

    • Phil Factor

      Thank you very much for the contribution. Yes, As I say in the summary, MONEY is a datatype that can be made to perform well and accurately if you know all the constraints and workarounds. I have used it myself in commercial applications. The reason that so many people warn against using it is that many developers aren’t aware of all the mistakes that can be made with the datatype. Even if the developers of an application get it all right, it seems to happen that a subsequent financial report written by a BI analyst who is unfamiliar with the datatype manages to put incorrect figures in front of business mangers.

  • Pingback: Shortchanged with International Money in SQL Server – SQL with Bert()

  • Pingback: Shortchanged with International Money in SQL Server - SQL with Bert()

  • Bruce H

    When you describe the bounds of the money data type, I think you mean trillion, not billion, e.g. 922,337,203,685,477.5807 (922,337 billion) should be (922,337 trillion). Suggest updating to reduce confusion. 😉

    • Phil Factor

      Thanks, Bruce. Thank goodness my wife holds the money when we go shopping. You’re right. I think it should be nine hundred and twenty-two trillion, three hundred and thirty-seven billion, two hundred and three million, six hundred and eighty-five thousand, four hundred and seventy-seven point five eight zero seven

    • Tony Davis

      good catch, @disqus_dUluJ6VO33:disqus . Fixed now.