How to store money in SQL Server
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 in our disposal. Microsoft provides us with ‘Exact Numerics’ and ‘Approximate Numerics’.
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 ‘float’ and ‘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;
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 4 decimal digits – in most cases it will be fine. It will be stored in 9 bytes according to storage type.
On the other hand money data type is 8 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 smallmoney data type available if you would need it, but it’srange is pretty small (- 214,748.3648 to 214,748.3647).
When you think about situation in which money data type can be used in SQL you will probably come to the conculsion that you can:
- add / subtract (for example to get sum of expenses)
- divide (for example to get % in KPI’s)
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;
--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;
CREATE SEQUENCE dbo.MoneyTest_id
START WITH 1
INCREMENT BY 1
CREATE TABLE dbo.MoneyTest(
id int NOT NULL PRIMARY KEY DEFAULT (NEXT VALUE FOR dbo.MoneyTest_id)
-- add some rows
INSERT INTO dbo.MoneyTest(
SELECT SUM(decimalMoney) AS [sumDecimal]
,SUM(moneyMoney) AS [sumMoney]
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 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.