Understanding the SQL Decimal data type

This article aims to walk you through the SQL Decimal data type and its usage with various examples. We will also see how we can exercise this data type in SQL Server to help make SQL developer’s job easier.

Introduction

Organizations deal with decimals on a day-to-day basis, and these decimal values can be seen everywhere in different sectors, be it in banks, the medical industry, biometrics, gas stations, financial reports, sports, and whatnot. Using whole numbers (by rounding decimal numbers) definitely makes one’s job easier but it often leads to inaccurate outputs, especially when we are dealing with a large number of values and crucial data. In such scenarios, it is ideal to use Sql Decimal data type in SQL Server to deliver correct results with perfect precision.

It becomes very essential for SQL developers to choose the correct data types in the table structure while designing and modeling SQL databases. Let’s move forward and explore Decimal data type in SQL Server.

Pre-requisite

SQL Decimal data type is being used in SQL Server since forever. You can use any SQL Server version installed (starting 2000 or above) to understand this data type. We will be using SQL Server 2017 in this article for the demo purposes. If you don’t have any version installed on your system and wish to practice against the 2017 version, download it from here.

The Basic syntax of Decimal data type in SQL Server

Let’s take a look at the basic syntax of SQL Decimal Data type first. It is denoted as below:

decimal [(p [,s])]

Where,

p stands for Precision, the total number of digits in the value, i.e. on both sides of the decimal point
s stands for Scale, number of digits after the decimal point

The default value of p is 18 and s is 0 and for both these values, the minimum is 1 and the maximum is 38.

In short, by defining parameters in the SQL Decimal data type, we are estimating how many digits a column or a variable will have and also the number of digits to the right of the decimal point.

For instance, decimal (4,2) indicates that the number will have 2 digits before the decimal point and 2 digits after the decimal point, something like this has to be the number value- ##.##.

One important thing to note here is, – parameter s (Scale) can only be specified if p (Precision) is specified. The scale must always be less than or equal to the precision.

Defining SQL Decimal Data type

Let’s work with a very popular mathematical constant – π, aka, Pi that has a value equal to 3.14159 (22/7 in a fraction). Copy and paste the below query in a new query window and execute it.

DECLARE @PiWithNoDecimal DECIMAL(6,0) = 3.14159

DECLARE @Piupto5Decimal DECIMAL(6,5) = 3.14159

DECLARE @Piupto1Decimal DECIMAL(3,1) = 3.14159

SELECT @PiWithNoDecimal AS PiWithNoDecimal, @Piupto5Decimal AS Piupto5Decimal, @Piupto1Decimal AS Piupto1Decimal

The above result set shows how SQL Server treats each combination of precision and scale as a different data type. Like here, decimal (6, 0) behaves differently from data types decimal (6,5) and decimal (3,1) and are considered as three different types. This way we can tweak the parameters in the SQL Decimal type to achieve desired results.

Now that we know how to create this Decimal data type in SQL Server, let’s explore it with numerous examples.

Using SQL Decimal in the Tables

Let’s quickly create a new table, named Patients, that makes use of decimal data type for columns height and weight. We will insert a few rows using an INSERT clause as shown below for the demo purposes.

CREATE TABLE dbo.Patients

( Name varchar(10),

Gender varchar(2),

Height decimal (3,2),

Weight decimal (5,2)

)

INSERT INTO PATIENTS VALUES('John','M',6.1,80.4)

INSERT INTO PATIENTS VALUES('Bred','M',5.8,73.7)

INSERT INTO PATIENTS VALUES('Leslie','F',5.3,66.9)

INSERT INTO PATIENTS VALUES('Rebecca','F',5.7,50.2)

INSERT INTO PATIENTS VALUES('Shermas','M',6.5,190.6)

Once the data is populated in the table, we can query this data using SELECT statement as shown below. The decimal values can be seen in the height and weight attributes.

1	SELECT * FROM dbo.PATIENTS

Let’s figure out what happens if we try to insert values that exceed the specified precision or scale values while defining the Height and Weight columns. For this demo, we will insert 2 more rows into this table (shown below).

1

INSERT INTO PATIENTS VALUES('Largest','M', '10.9', 88.5)
1

INSERT INTO PATIENTS VALUES('Hulk','M', '9.9', 1000.45)

It encounters the below error saying arithmetic overflow error and the SQL Server terminated the statements.

Let’s get to the root of this issue:

Height Decimal (3, 2) means the value can have 3 digits overall and 2 digits to the right of the decimal point. In the first line of code above, the value 10.9 (considered as 10.90 = 4 digits overall) exceeds the specified range (3, 2) and causes the overflow
Weight Decimal (5,2) means the total number of digits cannot exceed 5 and 2 digits can be placed to the right of the decimal. However, the value 1000.45 in the second line of code above exceeds the specified range of (5, 2) since it means 6 digits in total and throws an overflow error

Quick note – In the above error message, if you have noticed, “data type numeric” is stated instead of data type decimal, the reason is that the Decimal and the Numeric data type are exactly the same, both are fixed-precision data types and can be used interchangeably

Resolving the error

One of the easiest workarounds is to increase the precision level of the columns to store bigger numbers. We can alter the data type of the columns without dropping the table or column with the below code.

1 2	ALTER TABLE dbo.Patients ALTER COLUMN Height decimal(4,2) ALTER TABLE dbo.Patients ALTER COLUMN Weight decimal (6,2)

Once altered, execute the Insert queries to insert these rows into the table.

We can see the rows being added to the table.

Storage considerations with Decimal Data Type in SQL Server

Data type SQL Decimal requires the following storage bytes for the specified precision as provided by Microsoft below:

Precision	Storage (Bytes)
1 – 9	5
10 – 19	9
20 – 28	13
29 – 38	17

The space consumption of SQL Decimal data type is based on the column definition and not on the size of the value being assigned to it. For e.g. Decimal (12, 4) with value of 888.888 takes 9 bytes on disk and Decimal (22, 2) value of 9999.99 consumes 13 bytes on disk. This is why this data type falls under fixed-length columns.

As a SQL developer myself, I always try to use SQL Decimal data type as decimal (9, 2) which consumes the least storage, 5 bytes on disk and offers better performance.

Conclusion

I hope this article provides a comprehensible approach on how to use SQL Decimal data type. Always ensure the precision of the decimal or numeric variable specified is enough to accommodate the values assigned to it. Additionally, we observed, how selecting the right kind of data type helps SQL developers to save disk storage.

In case of any questions, please feel free to ask in the comments section below.

To continue your journey with SQL Server and data types used in it, I would recommend going through the below links.

Author
Recent Posts

Gauri Mahajan

Gauri is a SQL Server Professional and has 6+ years experience of working with global multinational consulting and technology organizations. She is very passionate about working on SQL Server topics like Azure SQL Database, SQL Server Reporting Services, R, Python, Power BI, Database engine, etc. She has years of experience in technical documentation and is fond of technology authoring.

She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.

View all posts by Gauri Mahajan