dec 22, 2014

“A colleague at work said the MONEY datatype in SQL Server is bad and shouldn’t be used. Are there good reasons behind this or is it just hearsay?”

Excellent question, Jamal. Before dealing with the question itself let’s consider some historical background. There’s nothing that can be done with the MONEY datatype in SQL Server that cannot also be done with the DECIMAL datatype. However, in early versions of SQL Server, the flexibility of the DECIMAL datatype came at the cost of requiring many more bytes for storage.

Quick Note: DECIMAL and NUMERIC are two different names for the same datatype. I generally say DECIMAL, but if you say NUMERIC that’s cool, too.

As you know, the MONEY datatype is fixed using four digits to the right of the decimal. By sacrificing flexibility, MONEY could be stored in fewer bytes (eight, in fact) than a DECIMAL datatype, which required 17. Money was preferable when saving bytes was important, in other words, always. The situation changed dramatically with the introduction of SQL Server 2005.

With Service Pack 2, SQL Server 2005 gained the vardecimal storage option. The number of bytes required to store the DECIMAL datatype varied depending on the number of decimal digits required. At the small end (we’ll talk about what small means shortly) only 5 bytes are required. DECIMAL datatypes require 5, 9, 13, or 17 bytes depending on the number being encoded.

Without vardecimal storage, DECIMAL data requires 17 bytes in a SQL Server table row regardless of precision.

If you want to keep the 4 bytes to the right of the decimal as does MONEY, the DECIMAL datatype will require only 5 bytes of storage until you hit 429496.7296, If two bytes to the right of the decimal are sufficient, as is often the case in commerce, the cutoff is exactly one hundred time larger at 42949672.96.

As a result, your tables may take up less space with the DECIMAL datatype if most of your values are below the cutoff, even when taking into account the two extra bytes required on the hard drive for variable length columns.

DECLARE @I DECIMAL(19,4)=429496.7295, @J DECIMAL(19,4) = 429496.7296

SELECT DATALENGTH(@I), DATALENGTH(@J)

Note that in the examples above we are looking at variables, so table structure is irrelevant to these results.

In SQL Server 2008 and later, the recommendation is to use row compression rather than vardecimal storage, making vardecimal one of the shortest-lived enhancements in SQL Server history. Both vardecimal storage and row compression are enterprise features, so if you are using the standard edition of SQL Server these considerations will not affect you.

The variable length of DECIMAL datatype variables in RAM is * not* an enterprise feature and applies to all editions of SQL Server including Express.

The number of bytes required for storage is a consideration for all datatypes. However, with numeric datatypes a second potential issue must be considered: the precision of numerical calculations. The MONEY datatype in SQL server is intrinsically less precise than the DECIMAL datatype in calculations.

The MONEY datatype can, in principle, introduce roundoff error into calculations. Roundoff error will never appear in calculations involving only addition and subtraction, which constitute the majority of calculations performed on MONEY. (Roundofferror can appear in addition and subtraction in the floating point datatypes FLOAT and REAL.) While MONEY is safe for addition and subtraction, the minute division or multiplication is introduced, all bets are off. This arises from the fact that SQL Server does arithmetic differently depending on the datatypes involved in the calculation. For example –

SELECT 23/7 returns 3, since SQL Server has done integer division. If SQL Server performs calculations on the MONEY datatype, intermediate values are stored internally as MONEY datatypes. Since MONEY only represents four places to the right of the decimal, any calculation requiring 5 or more places in the fractional representation will incur roundoff error.

Calculations that incur roundoff error are not rare. For example, an analyst might wish to calculate the fraction that each product contributed to overall sales during some time period. For products that contribute a small fraction of overall sales, the calculated percentage can be considerably off. The absolute magnitude of the error will be small, but we’re calculating a proportion here, and the proportional error can be enormous.

The DECIMAL datatype behaves differently, if DECIMAL datatypes are manipulated arithmetically, DECIMAL math is used, but the values are not limited to the scale and scale declared for the input.

DECLARE @M MONEY = 1234, @D DECIMAL(6,2) = 1234

SELECT @M/$1000000 AS [MONEY] , @D/$1000000 AS [DECIMAL]

Notice that in the above calculation, the DECIMAL variable has been specified for only two places to the right of the decimal.

1) If you are not using the Enterprise edition and cannot use row compression, the MONEY datatype in SQL server can save substantial space when compared with DECIMAL. Don’t forget that if you are not storing currency values greater than 214,748.3647, SMALLMONEY might be a better choice.

2) If you are going to use MONEY data in a calculation involving multiplication or division, it is prudent to cast to the DECIMAL datatype.

3) There is no good reason to use the MONEY datatype in SQL server for a T-SQL variable in a stored procedure, batch, or function.

Please don’t hesitate to drop a note if you have any questions or observations on SQL Server and numeric datatypes.