DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • SQL Server to Postgres Database Migration
  • Implementing Infinite Scroll in jOOQ
  • CockroachDB TIL: Volume 11
  • JSON-Based Serialized LOB Pattern

Trending

  • TypeScript: Useful Features
  • Decoding Business Source Licensing: A New Software Licensing Model
  • Hugging Face Is the New GitHub for LLMs
  • Log Analysis Using grep
  1. DZone
  2. Data Engineering
  3. Data
  4. How to Store Money in SQL Server

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.

Mateusz Komendołowicz user avatar by
Mateusz Komendołowicz
·
Jul. 14, 17 · Tutorial
Like (3)
Save
Tweet
Share
51.90K Views

Join the DZone community and get the full member experience.

Join For Free

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: 

approximatenumbersexactnumbers

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

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.

storagevbytesdecimal

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:

moneydecimal

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.

sql Data Types

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

Opinions expressed by DZone contributors are their own.

Related

  • SQL Server to Postgres Database Migration
  • Implementing Infinite Scroll in jOOQ
  • CockroachDB TIL: Volume 11
  • JSON-Based Serialized LOB Pattern

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: