{{announcement.body}}
{{announcement.title}}

# How to Store Money in SQL Server

DZone 's Guide to

# How to Store Money in SQL Server

### See how to store financial figures like amounts in currency or FX rates in SQL Server, which is often required when constructing databases or data warehouses.

· Database Zone ·
Free Resource

Comment (0)

Save
{{ articles[0].views | formatCount}} Views

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 `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;``````

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
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
)
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.

Topics:
data warehouse, database, sql server, tutorial

Comment (0)

Save
{{ articles[0].views | formatCount}} Views

Published at DZone with permission of Mateusz Komendołowicz , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.