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
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
NUMERIC type, which is less likely to suffer from rounding errors or scale overflow.
A recommendation to avoid use of the
SMALLMONEY datatypes is included as a “Best Practice” code analysis rule in SQL Prompt (BP022).
Rounding errors when using MONEY datatype
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 billion) and 922,337,203,685,477.5807 (922,337 billion).
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.
DECLARE @MoneyTable TABLE (Total MONEY, Portion MONEY);
INSERT INTO @MoneyTable (Total, Portion)
($271.00, $199.50), ($4639.00, $4316.00), ($8031.00, $7862.00), ($7558.00, $7081.00),
($9912.00, $9547.00), ($389.00, $179.00), ($4495.00, $4214.00), ($2844.00, $2398.00),
($265.67, $124.33), ($4936.56, $967.54);
SELECT Portion, Total, (Portion / total)*100 AS Percentage FROM @MoneyTable
Here are the results:
Notice the lack of any currency symbols for the
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).
DECLARE @MoneyTable TABLE (Total DECIMAL(19, 4), Portion DECIMAL(19, 4));
INSERT INTO @MoneyTable (Total, Portion)
(271.00, 199.50), (4639.00, 4316.00), (8031.00, 7862.00), (7558.00, 7081.00),
(9912.00, 9547.00), (389.00, 179.00), (4495.00, 4214.00), (2844.00, 2398.00),
(265.67, 124.33), (4936.56, 967.54);
cast((Portion / Total) * 100 as numeric(19,2)) AS percentage
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
Portion, then you’ll still get the correct percentage values (under the covers, SQL Server implicitly converts the monetary values to
(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
percentage. The fact that we cannot calculate the
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.
Basically, it pays to do calculations in
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.
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.
Also in Hub
Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain o...
Also in Product learning
People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that b...
Also in SQL Prompt
We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table.
Also about SQL code smells
Most of us in the data management industry will have learned to adapt, in recent years, to 'agile' development and deployment practices. Many organizations have invested heavily in the tools and proce...
Also about SQL Prompt
When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a bat...
Also about static code analysis
It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subqu...