SQL Money Data Type vs. SQL Decimal for Monetary Values in SQL Server

Problem

I recently started developing SQL Server solutions with monetary values. The SQL literature expresses varying opinions about using the money or the decimal data type for displaying and computing results for monetary values. Please provide some examples that contrast the use of these two data types with a special emphasis on obtaining the most accurate outcomes for monetary values.

Solution

If you implement database applications long enough, there is a high chance that some or many of these apps will include the gathering, processing, visualizing, or modeling of monetary values. This tip aims to help you make good decisions about processing monetary values in SQL Server apps.

There are lots of different use cases for processing monetary data values. My recent work focuses on price data for financial securities, such as stock shares, exchange-traded funds, or commodities. This work involves collecting monetary data, computing share price indicators, visualizing monetary values and their indicators, and modeling buy and sell decisions to accumulate financial gains.

In preparing this tip, I was reminded of other SQL Server use cases for monetary data. These use cases include preparing sales reports, invoicing clients, and standard versus actual cost reports. The loan industry uses SQL Server to track account balances, compute interest and fee charges, and monitor payments.

Quick Review of Money and Decimal Data Types for Storing and Processing Monetary Values

Whatever the requirements for your financial app, it is likely that there is a high emphasis on values being accurate. Your choice about which data type to use for monetary values can impact the accuracy of your app. Currency values in a financial app can be positive or negative. For example, a discount on an invoice might have a negative value so that it can be summed along with the cost per line item ordered. Monetary values for the buy and sell prices of securities typically represent values for up to four places after the decimal point. In comparison, invoices typically represent prices to two places to the right of the decimal point.

Monetary data types for a financial app should be large enough to represent the maximum value for a sum of monetary values or a difference between monetary values. The SQL Server smallmoney data type, which stores values in 4-byte memory fields, can accommodate values in the range of – 214,748.3648 to 214,748.3647. Therefore, if the sum of a set of monetary values is outside the range of – 214,748.3648 to 214,748.3647, then SQL Server returns an overflow error instead of a sum. When this is possible, consider using another data type, such as the money data type. The money data type stores values in 8-byte memory fields. This data type can accommodate values in the range of – 922,337,203,685,477.5808 to 922,337,203,685,477.5807. Therefore, one solution to potential overflows from the sum of values with the smallmoney data type is to switch the money data type.

Another common data type used for monetary values in SQL Server is the decimal data type with a decimal(19,4) specification. This specification allows for the designation of monetary values with up to 19 total digits; 4 of the 19 digits are reserved for places after the decimal point. The storage requirement for each decimal(19,4) value is 9 bytes. The range of values for a decimal(19,4) specification is – 999,999,999,999,999.9999 to 999,999,999,999,999.9999.

The upper range values for money data type values and decimal(19,4) monetary values are different but not practically important. The upper limit values are 922 trillion monetary units for the money data type and 999 trillion monetary units for the decimal(19,4) specification. To put these upper limit values, consider the size of the U.S. budget in fiscal year 2022. The U.S. Treasury reports the budget size as approximately 5.35 trillion US dollars. Either of these upper limits dwarfs the size of the U.S. budget in fiscal year 2022.

On the other hand, there is a reliable difference between money data type values and decimal(19,4) specification values when division or multiplication calculations are performed. The results extend beyond four places to the right of the decimal point. This is because the money data type can truncate digits of calculated results beyond four places to the right of the decimal point. Still, the decimal(19,4) specification does not truncate the digits of calculated results. This difference only pertains to division and multiplication calculations; it does not pertain to addition or subtraction calculations.

Examples of Local Variables Having Money Data Type or Decimal(19,4) Values

The differences between calculations with money values versus decimal(19,4) values are easy to follow when using local variables. This section presents four examples – three showing differences in calculated results and one not showing a difference.

The following script presents examples.

• The initial DECLARE statement specifies four local variables.
  • The first two local variables are named @decimal_1 and @decimal_2. Both variables are specified with decimal(19,4).
  • The second two local variables are named @money_1 and @money_2. Each of these variables is specified with a money data type.
• The next code block consists of two SELECT statements. These two statements comprise the first example. A line of comment markers separates the select statements for the first example from the preceding DECLARE statement.
  • The first SELECT statement assigns values to @decimal_1, @decimal_2, @money_1, and @money_2.
  • The second statement presents two examples – 1) @decimal_1 and @decimal_2 and 2) @money_1 and @money_2.
    • The first four columns within the results set are for @decimal_1 and @decimal_2.
      • The first two columns display the values of @decimal_1 and @decimal_2.
      • The third column shows the expression for a calculation based on @decimal_1 and @decimal_2.
      • The fourth column presents the value generated by the calculation.
    • The second four columns within the results set shows comparable information for @money_1 and @money_2.
      • The first two columns display the values of @money_1 and @money_2.
      • The third column shows the expression for a calculation based on @money_1 and @money_2.
      • The fourth column presents the value generated by the calculation.
• The next two code blocks are for the second and third examples.
  • The code for each example is separated from the preceding one by a line of comment markers.
  • The code for the first, second, and third examples has an identical structure, but the local variable values differ for each example.
  • The purpose of the examples is to show the impact of the local variable assignments and data types on the calculated value for the expression in each example.
• The final code block is for the fourth example.
  • This example uses a different expression than the first, second, and third examples.
    • The expression in the final example computes a percentage value based on the division of monetary values. The percentage value is computed based on dividing one monetary value by another.
    • The expression for the preceding three examples computes a monetary value based on the division of one monetary value by another monetary value and the multiplication of the division’s quotient by the divisor value from the division.

-- declare these variables for all the examples
DECLARE @decimal_1 DECIMAL(19,4),
@decimal_2 DECIMAL(19,4),
@money_1 MONEY,
@money_2 MONEY;
 
------------------------------------------------------------------------------
 
-- first example
SELECT @decimal_1 = 3,
@decimal_2 = 11,
@money_1 = 3,
@money_2 = 11;
 
SELECT 
 @decimal_1 [@decimal_1]
,@decimal_2 [@decimal_2]
,'(@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult' [expression]
,(@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult
,@money_1 [@money_1]
,@money_2 [@money_2]
,'(@money_1/@money_2)*@money_2 AS MoneyResult' [expression]
,(@money_1/@money_2)*@money_2 AS MoneyResult;
 
------------------------------------------------------------------------------
 
-- second example
SELECT @decimal_1 = 3,
@decimal_2 = 17,
@money_1 = 3,
@money_2 = 17;
 
SELECT 
 @decimal_1 [@decimal_1]
,@decimal_2 [@decimal_2]
,'(@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult' [expression]
,(@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult
,@money_1 [@money_1]
,@money_2 [@money_2]
,'(@money_1/@money_2)*@money_2 AS MoneyResult' [expression]
,(@money_1/@money_2)*@money_2 AS MoneyResult;
 
------------------------------------------------------------------------------
 
-- third example
SELECT 
@decimal_1 = 3,
@decimal_2 = 15,
@money_1 = 3,
@money_2 = 15;
 
SELECT 
 @decimal_1 [@decimal_1]
,@decimal_2 [@decimal_2]
,'(@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult' [expression]
,(@decimal_1/@decimal_2)*@decimal_2 AS DecimalResult
,@money_1 [@money_1]
,@money_2 [@money_2]
,'(@money_1/@money_2)*@money_2 AS MoneyResult' [expression]
,(@money_1/@money_2)*@money_2 AS MoneyResult;
 
------------------------------------------------------------------------------
 
-- fourth example
SELECT 
@decimal_1 = 189.50,
@decimal_2 = 301.00,
@money_1 = 189.50,
@money_2 = 301.00;
 
-- cast results as numeric(19,2) to show just 2 places after decimal point
select 
 @decimal_1 [@decimal_1]
,@decimal_2 [@decimal_2]
,'cast(((@decimal_1/@decimal_2) * 100) as numeric(19,2)) Percentage_decimal' [expression]
,cast(((@decimal_1/@decimal_2) * 100) as numeric(19,2)) Percentage_decimal
, @money_1 [@money_1]
,@money_2 [@money_2]
,'cast(((@money_1/@money_2) * 100) as numeric(19,2))' [expression]
,cast(((@money_1/@money_2) * 100) as numeric(19,2)) as Percentage_money

The following screenshot displays four result sets – one each from the first through the fourth examples.

• As the preceding code shows, the first example assigns a value of 3 to both @decimal_1 and @money_1. This example also assigns a value of 11 to both @decimal_2 and @money_2.
  • Although values of 3 and 11 are assigned to @decimal_1 and @decimal_2, the local variables in the results set appear, respectively, as 3.0000 and 11.0000. The same two values are assigned to @money_1 and @money_2, but these two local variables appear with just two, instead of four, zeros after the decimal point.
  • The first expression in the example divides @decimal_1 by @decimal_2 and then multiplies the outcome of the division by @decimal_2. Because these are decimal values, the outcome from the expression has a value of 3; it appears in the results set with six zeros after the decimal point.
  • The second expression in the example divides @money_1 by @money_2 and then multiplies the outcome by @money_2. Instead of returning an outcome value of 3, this second expression returns a value of 2.9997. This expression with money data types returns a slightly invalid outcome. The invalid outcome is due to the truncation issue in the previous section.
• The second example appears in the second results set for the screenshot below.
  • This example uses input values of 3 and 17, respectively, for @decimal_1 and @money_1 versus @decimal_2 and @money2.
  • As you can see, the expression for the second example is identical to the one used in the first example.
  • Also, the DecimalResult column value (3) is correct.
  • However, the MoneyResult column value (2.9988) is slightly short of the correct outcome. This is, again, because of truncated output from multiplication and/or division calculations with money data type values.
• The third example appears in the third results set below.
  • The most noteworthy feature of the results set is that the MoneyResult column value equals the DecimalResult column value, and both column values are correct.
  • Three fifteenths (3/15) multiplied by 15 exactly equals 3.
  • The quotient of 3 divided by 15 equals 0.2. When this quotient is multiplied by 15, as specified in the expression for the third example, the result is precisely 3 for both money data type and decimal data type values. The first and second examples have remainders for @money_1 divided by @money_2; these remainders set up an opportunity for truncation, which is missing from the third example.
• The last results set in the following screenshot is for the fourth example.
  • The expression in this example casts a quotient as a numeric data type with a (19,2) specification. The numeric data type is a synonym in SQL Server for the decimal data type.
  • The expressions for the calculations in the fourth example compute the percentages of @decimal_1 and @money_1, respectively, to @decimal_2 and @money2.
  • The cast of the quotient multiplied by 100 with a numeric (19,2) specification reports the percentage values to two places after the decimal point.
  • As you can see, the Percentage_money column value (62.95) is .01 percentage points less than the Percentage_decimal value (62.96). Truncation is, again, the cause for this difference in outcomes. The next section dives deeper into how truncation can cause observed differences between calculations computed with money data type values versus decimal data type values.

Comparing Column Computed Values from Money Data Type Versus Decimal Data Type

The prior section introduced money data type versus decimal data examples for calculations with local variables. Still, typical data management chores, business intelligence applications, and data science projects rely much more heavily on tables and results sets derived from them than local variables. This section switches the focus to comparing columns from results sets with computed values based on either money data type values or decimal data type values. You will also discover in this section more in-depth coverage of why truncation occurs in calculations with money data type values.

This section provides examples for three main functions.

• It pulls data from a reference table and populates two additional tables with either money data values or decimal data type values.
• It joins selected values from the money data type and decimal data type tables for a side-by-side visual inspection of the joined results.
• It computes quotients based on either money data type or decimal data type values. Then, the quotient values and their differences are examined before and after rounding to four places after the decimal point.

Pulling the Base Source Data and Populating Two Additional Tables with Values for Each Data Type

The following script pulls data from the yahoo_finance_ohlcv_values_with_symbol table of the dbo schema within a database named DataScience. The process for originally creating and populating the table is described in a tip titled “A Framework for Comparing Time Series Data from Yahoo Finance and Stooq.com“. The prior tip’s download contains CSV files with open, high, low, close, and volume values for over 60 ticker symbols for many years of data. The code below shows data extraction for just the Apple ticker symbol (AAPL) in January 2021. The extracted data are deposited in a fresh copy of the source_data table.

use DataScience
 
drop table if exists dbo.source_data
 
-- close prices for AAPL in January 2021 in dbo.source_data
select symbol, date, [close]
into dbo.source_data
from dbo.yahoo_finance_ohlcv_values_with_symbol
where symbol = 'AAPL' and year(date) = 2021 and month(date) = 1
order by date
 
-- optionally display source_data
-- select * from source_data

The next code segment shows how to derive two separate tables from the source_data table.

• The first table is named Values_with_money_data_type. The columns in the table are named symbol, date, and [close]. The close column is explicitly specified with a money data type.
• The second table is named Values_with_decimal_data_type. This table is the same as the first table, except that its close column has a data type specification of decimal(19,4).
• Additional code in the script assigns a primary key constraint to each of the two tables derived from yahoo_finance_ohlcv_values_with_symbol. Designating primary key constraints can speed up downstream processing, such as the operations coded in the next two subsections.

-- create a fresh copy of Values_with_money_data_type
drop table if exists dbo.Values_with_money_data_type
 
create table Values_with_money_data_type (
 symbol nvarchar(10) not null
,date date not null
,[close] money null
)
 
alter table dbo.Values_with_money_data_type
 add constraint pk_symbol_date_money_tbl primary key(symbol, date)
 
-- populate Values_with_money_data_type
insert into dbo.Values_with_money_data_type
select * from dbo.source_data
 
 
 -- create a fresh copy of Values_with_decimal_data_type
drop table if exists dbo.Values_with_decimal_data_type
 
create table Values_with_decimal_data_type (
 symbol    nvarchar(10) not null
,date date not null
,[close] decimal(19,4)  null
)
 
alter table dbo.Values_with_decimal_data_type
 add constraint pk_symbol_date_decimalimal_tbl primary key(symbol, date)
 
-- populate Values_with_decimal_data_type
insert into dbo.Values_with_decimal_data_type
select * from dbo.source_data

Joining the Data for Performing Identical Division Calculations for the Money and Decimal Data Type Columns

The next code segment concludes the compilation of the basic data for this section. The SELECT statement in the segment joins the Values_with_money_data_type and Values_with_decimal_data_type tables to the source_data table by symbol and date. The select list draws on columns from each of the three tables.

• The first three columns are from the source_data table. These columns are for symbol, date, and close column values. The close column from source_data is assigned an alias of close_from_source_data.
• The fourth column of the results set from the select statement is the close column values from the Values_with_money_data_type table. This column has an alias of close_with_money_data_type.
• The fifth column of the results set from the select statement is the close column values from the Values_with_decimal_data_type table. This column has an alias of close_with_decimal_data_type.

-- compare raw values
select
 source_data.symbol
,source_data.[date]
,source_data.[close] close_from_source_data
,Values_with_money_data_type.[close] close_with_money_data_type
,Values_with_decimal_data_type.[close] close_with_decimal_data_type
from dbo.Values_with_money_data_type
 
inner join dbo.source_data
 on (Values_with_money_data_type.symbol = source_data.symbol and
    Values_with_money_data_type.date = source_data.date)
 
inner join dbo.Values_with_decimal_data_type
 on (Values_with_money_data_type.symbol = Values_with_decimal_data_type.symbol and
    Values_with_money_data_type.date = Values_with_decimal_data_type.date)
 
order by  Values_with_money_data_type.symbol
,Values_with_money_data_type.[date]

Here is the results set for the inspection of the joined results. There are three columns with close prices. The remainder of this tip drills down on computing derived values from the close_with_money_data_type and close_with_decimal_data_type columns. As you can see from the following results set, these two columns show identical close values. Each column is based on the close prices from the source_data table. However, the last two columns have explicitly different data types. The data types are specified in the column names.

• A money data type value displays its contents in up to four digits after the decimal point. If the value does not need any four digits to represent its value, then they do not show.
• A decimal data type value with decimal(19,4) specification always shows all four digits after the decimal point.
  • For example, the close_with_money_data_type column value for 2021-01-04 shows as 129.41. As you can see, the second two places after the decimal point are empty.
  • However, the close_with_decimal_type column value for 2021-01-04 shows as 129.4100. This value is the same as the close_with_money_data_type column value, but all four places after the decimal point are populated.

Performing and Analyzing Calculations for Money Data Type and Decimal Data Type Close Price Columns

This subsection shows the outcome of dividing by 3 the close_with_money_data_type column values and close_with_decimal_type column values. Quotients are displayed two ways –with and without rounding to four places after the decimal point. Recall that division is one of the two arithmetic operations that can cause truncation for calculated values with a money data type value.

The code for the first way appears next. The script joins the Values_with_money_data_type table with the Values_with_decimal_data_type table by symbol and date column values. There are seven columns in the results set.

• The first two columns are for symbol and date.
• The third column is for the close column from the Values_with_money_data_type table.
• The fourth column is for the close column from the Values_with_decimal_data_type table.
• The fifth column displays close column values from the Values_with_money_data_type table divided by 3.
• The sixth column displays close column values from the Values_with_decimal_data_type table divided by 3.
• The seventh column displays the sixth column value less the fifth column value.
  • If the division operation in the fifth and sixth columns returns the same value, the seventh column value is 0.
  • A value different than 0 indicates that the two divisions on a row return different quotients.

-- comparing money data type and decimal data type close values divided by 3
select 
 mon.symbol
,dec.date
,mon.[close] close_with_money_data_type
,dec.[close] close_with_decimal_data_type
,mon.[close]/3 [close_divided_by_3_with_money_data_type]
,dec.[close]/3 [close_divided_by_3_with_decimal_data_type]
,(dec.[close]/3) - (mon.[close]/3) [decimal quotient less money quotient]
--,cast(mon.[close]/3 as numeric(19,4)) [close_divided_by_3_with_money_data_type cast as numeric(19,4)]
--,cast(dec.[close]/3 as numeric(19,4))  [close_divided_by_3_with_decimal_data_type cast as numeric(19,4)]
--,(cast(dec.[close]/3 as numeric(19,4)) - cast(mon.[close]/3 as numeric(19,4))) [casted decimal quotient less casted money quotient]
 
from dbo.Values_with_money_data_type mon
inner join dbo.Values_with_decimal_data_type dec
on mon.symbol = dec.symbol and
   mon.date = dec.date

Here is the results set from the preceding script. Here are several particularly important points in the results set.

• The seventh column is not always 0. More than half the row values in the column are not equal to 0.
• The seventh column values are always 0 or greater than 0.
  • For 6 of the 19 rows in the results set, the difference in the seventh column is 0. For these six rows, the quotients in the fifth and sixth columns are exactly equal.
  • For 13 of the 19 rows in the results set, the difference shows that the quotient based on the money data type is less than the quotient based on the decimal data type.
  • For example, for January 4, 2021, the difference in the seventh column is 0.000066.
• Finally, the quotients derived from decimal data type values in the sixth column always show 6 places after the decimal point. On the other hand, the quotients derived from money data values in the fifth column always show 4 or fewer places after the decimal point. The fewer places after the decimal point for the quotients based on money data type values point to rows that depict the truncation that occurs for division with the money data type.

As indicated in the “A quick review of money and decimal data types for storing and processing monetary values” section, money values for security prices are frequently represented with up to 4 places after the decimal point. You can round the values in the fifth, sixth, and seventh columns to four places after the decimal point by casting them with a numeric(19,4) data type specification. The following script shows the syntax for accomplishing this.

-- comparing money data type and decimal data type close values divided by 3
-- with a cast statement for rounding values and difference to 4 places after decimal point
select
 mon.symbol
,dec.date
,mon.[close] close_with_money_data_type
,dec.[close] close_with_decimal_data_type
--,mon.[close]/3 [close_divided_by_3_with_money_data_type]
--,dec.[close]/3 [close_divided_by_3_with_decimal_data_type]
--,(dec.[close]/3) - (mon.[close]/3) [decimal quotient less money quotient]
,cast(mon.[close]/3 as numeric(19,4)) [close_divided_by_3_with_money_data_type cast as numeric(19,4)]
,cast(dec.[close]/3 as numeric(19,4))  [close_divided_by_3_with_decimal_data_type cast as numeric(19,4)]
,(cast(dec.[close]/3 as numeric(19,4)) - cast(mon.[close]/3 as numeric(19,4))) [casted decimal quotient less casted money quotient]
 
from dbo.Values_with_money_data_type mon
inner join dbo.Values_with_decimal_data_type dec
on mon.symbol = dec.symbol and
   mon.date = dec.date

Here is the results set from the preceding script.

• This results set is very similar to the one shown in the preceding screenshot. The most prominent difference is the rounding to four places after the decimal for values in the fifth, sixth, and seventh columns. This rounding operation may be important to clients who prefer to view the money values with no more than four places after the decimal point.
• It may be worth pointing out that the most significant difference for any row is never larger than .0001. This difference value is a tiny fraction of the average money value on the rows.
  • For new applications, it may be true that using decimal values for money values is superior in terms of accuracy when division and/or multiplication is used for computing monetary values.
  • However, for legacy applications, it may or may not be worth refactoring long, complicated solutions to improve accuracy (which is likely to be minuscule). This may depend on the application requirements and the cost in terms of time and effort to perform refactoring and redeploy a legacy solution that uses the decimal data type instead of the money data type.

Next Steps

The main point of this tip is that the money data type and the decimal(19,4) specification can yield different results in numerical calculations. While the differences between computed money and decimal(19,4) values may be small for each computed value, they can add up in a large table encompassing calculations from hundreds of thousands or even millions of rows. Furthermore, the nature of the error for money computed values is that they understate the amount of the computed values because of truncation.

This tip’s download contains three files. Two of these files are for the SQL examples described in the tip. Before running the examples, you need to load the open, high, low, close, and volume data in a CSV file within this tip’s download into a SQL Server table named yahoo_finance_ohlcv_values_with_symbol. Furthermore, there are 60 additional CSV files with open, high, low, close, and volume data in this prior tip (A Framework for Comparing Time Series Data from Yahoo Finance and Stooq.com). You can try the code with these additional ticker symbols if you wish.

Before closing this tip, I want to invite you to write a short comment for this tip about one or more financial apps that you either developed or have received a request to develop. If possible, mention what data type you used or plan to use to store and process monetary data. Please say why you selected that data type for monetary values.