Understanding the SQL Decimal data type
Understanding the SQL Decimal data type
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 Latest posts by Gauri Mahajan (see all) Managing schema in Azure Database for PostgreSQL using pgAdmin - October 25, 2022 Reporting data from Azure Database for PostgreSQL using Power BI - October 12, 2022 Data Modeling with ERD features in pgAdmin - October 11, 2022
SQLShack
SQL Server training EspañolUnderstanding the SQL Decimal data type
July 15, 2019 by Gauri Mahajan 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. 1234 DECLARE @PiWithNoDecimal DECIMAL(6,0) = 3.14159DECLARE @Piupto5Decimal DECIMAL(6,5) = 3.14159DECLARE @Piupto1Decimal DECIMAL(3,1) = 3.14159SELECT @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. 1234567891011 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 interchangeablyResolving 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. 12 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. Spatial SQL data types in SQL Server SQL Server Data Type Conversion Methods and performance comparison Understanding the GUID data type in SQL Server A step-by-step walkthrough of SQL Inner Join Author Recent Posts Gauri MahajanGauri 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 Latest posts by Gauri Mahajan (see all) Managing schema in Azure Database for PostgreSQL using pgAdmin - October 25, 2022 Reporting data from Azure Database for PostgreSQL using Power BI - October 12, 2022 Data Modeling with ERD features in pgAdmin - October 11, 2022