Today, I would like to present another intriguing and challenging topic in SQL. When constructing databases or data warehouses, it may be required to store financial figures like amounts in currency or FX rates in SQL Server.

Let’s take a look at tools at our disposal. Microsoft provides us with Exact Numerics and Approximate Numerics.

Examples: 

But because we will need to store financial figures, we will get values with variable decimals. So to start, we can use:

• FLOAT/REAL (different range, storage)
• DECIMAL/NUMERIC (almost the same, functional equivalence)
• MONEY/SMALLMONEY

The main problem with FLOATand REAL is that they are approximate numerics, so they don’t store exact values. Example:

DECLARE @f AS FLOAT = '29545428.0211111';
SELECT CAST(@f AS NUMERIC(28, 14)) AS value;

Result:

FLOAT has a not-known, non-deterministic precision. So you should never use float or real for storing money in SQL Server.

Money vs. Decimal

OK, let’s compare MONEY vs. DECIMAL.

Let’s assume that we will use DECIMAL (19,4). It will allow us to store maximum 19 of total numbers and four decimal digits — in most cases, it will be fine. It will be stored in nine bytes according to storage type.

On the other hand, the MONEY data type is eight bytes. MONEY’s range is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, so DECIMAL (19,4) can store any value that fits money. There is also A SMALLMONEY data type available if you would need it, but its range is pretty small (- 214,748.3648 to 214,748.3647). 

When you think about a situation in which the MONEY data type can be used in SQL, you will probably come to the conclusion that you can:

• Add/subtract (for example, to get the sum of expenses).
• Divide (for example, to get a % in KPIs).

Let’s take a look at this short example that I have prepared:

--check if table exists, if so drop it
IF OBJECT_ID(N'dbo.MoneyTest', N'U') IS NOT NULL
DROP TABLE dbo.MoneyTest;
GO
--check if sequence for id exists, if so drop it
IF OBJECT_ID('dbo.MoneyTest_id', N'SO') IS NOT NULL
DROP SEQUENCE dbo.MoneyTest_id;
GO
--create sequence
CREATE SEQUENCE dbo.MoneyTest_id
AS bigint 
START WITH 1
INCREMENT BY 1
MINVALUE 1;
GO
--create table
CREATE TABLE dbo.MoneyTest(
id int NOT NULL PRIMARY KEY DEFAULT (NEXT VALUE FOR dbo.MoneyTest_id)
,decimalMoney decimal(19,4)
,moneyMoney money
)
-- add some rows
INSERT INTO dbo.MoneyTest(
decimalMoney 
,moneyMoney)
VALUES 
(12321423442.3456,12321423442.3456)
,(1111111.1919,1111111.1919)
--check sums
SELECT SUM(decimalMoney) AS [sumDecimal] 
,SUM(moneyMoney) AS [sumMoney]
FROM dbo.MoneyTest
SELECT *
FROM dbo.MoneyTest
--compute variables
DECLARE @moneyPer money, @decimalPer decimal(19,4)
SET @moneyPer = (SELECT moneyMoney FROM dbo.MoneyTest WHERE id = 2)/((SELECT moneyMoney FROM dbo.MoneyTest WHERE id = 1))
SET @decimalPer = (SELECT decimalMoney FROM dbo.MoneyTest WHERE id = 2)/((SELECT decimalMoney FROM dbo.MoneyTest WHERE id = 1))
SELECT @moneyPer AS[moneyPer], @decimalPer AS [decimalPer];

And the results are:

MONEY and DECIMAL are useful in the case of values and sums. However, money is not a correct data type in case of division (The result is 0,00009 so it should be rounded to 0,0001).

To sum up, if you have an OLTP-like case and you store values like 1000.24 USD, I would suggest storing values in MONEY or SMALLMONEY data types. If you have an OLAP-like case where division or multiplication operations might occur, I would suggest going with DECIMAL data type.