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.
But because we will need to store financial figures, we will get values with variable decimals. So to start, we can use:
REAL(different range, storage)
NUMERIC(almost the same, functional equivalence)
The main problem with
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
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:
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
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.