Decimals are a common data type in SQL Server used to store numeric values with fixed precision and scale. In this article, we will dive into the syntax, usage, precision and scale ranges, maximum storage sizes, and synonyms for the DECIMAL data type in SQL Server.
We will also provide an example of creating a table with DECIMAL and NUMERIC columns, querying data from the table, and attempting to insert values exceeding precision and scale.
Syntax and usage of DECIMAL data type
The DECIMAL data type in SQL Server is used to store numeric values with fixed precision and scale. The syntax for DECIMAL is as follows:
DECIMAL(p, s)
Here, p represents the precision or the total number of digits that can be stored, and s represents the scale or the number of digits to the right of the decimal point.
For example, if p is 5 and s is 2, then the maximum number of digits that can be stored is 999.99 (5 digits including 2 decimal places). The MINIMUM precision value for DECIMAL is 1, while the MAXIMUM precision value that can be supported is 38.
The minimum scale value for DECIMAL is 0, while the maximum scale value is equal to the precision value.
Precision and scale ranges and default values
As stated earlier, the precision and scale ranges for DECIMAL data type in SQL Server are 1 to 38. The default precision value for DECIMAL is 18, while the default scale value is 0.
This means that if no precision or scale is specified while defining the DECIMAL data type, then the default values will be used.
Maximum storage sizes based on precision
The maximum storage size for the DECIMAL data type in SQL Server is determined by the precision value specified while defining the data type. The storage size is in bytes and is calculated as (p/2)+1.
For example, if the precision is 10, then the storage size will be (10/2)+1=6 bytes. Below is a table showing the maximum storage size for different precision values:
Precision Size (bytes)
1-9 5
10-19 9
20-28 13
29-38 17
Synonyms for DECIMAL data type
In addition to DECIMAL, there are two other synonyms for this data type – NUMERIC and DEC. These synonyms can be used interchangeably with the DECIMAL data type to represent fixed-point decimal values.
Example of creating a table with DECIMAL and NUMERIC columns
Let us consider an example of creating a table with DECIMAL and NUMERIC columns in SQL Server. We will create a table named ‘Employees’ and define two columns – ‘Salary’ as DECIMAL and ‘Bonus’ as NUMERIC.
The code for creating the table is as follows:
CREATE TABLE Employees
(
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2),
Bonus NUMERIC(8,4)
)
The above code creates a table called ‘Employees’ with four columns – EmpID, EmpName, Salary, and Bonus. The Salary column has a precision of 10 and a scale of 2, while the Bonus column has a precision of 8 and a scale of 4.
Querying data from the created table
Once the table is created, we can insert data into the table and query it. The code for inserting data and querying it is as follows:
INSERT INTO Employees(EmpID, EmpName, Salary, Bonus)
VALUES (1, 'John Smith', 50000.50, 0.1234)
SELECT * FROM Employees
The above code inserts a row into the Employees table and queries all the rows in the table.
The output of the SELECT statement will be as follows:
EmpID EmpName Salary Bonus
1 JOHN SMITH 50000.50 0.1234
Attempting to insert values exceeding precision and scale
One of the benefits of using the DECIMAL data type is to restrict values to a fixed precision and scale. Let us consider an example where we try to insert a value exceeding the precision and scale of the Salary column in the Employees table.
The code for inserting such a value is as follows:
INSERT INTO Employees(EmpID, EmpName, Salary, Bonus)
VALUES (2, 'Jane Doe', 9999999999.99, 0.5678)
When executed, the above code will result in an error because the value 9999999999.99 exceeds the precision of 10 and scale of 2 defined for the Salary column. In conclusion, the DECIMAL data type in SQL Server is useful for storing fixed-point decimal values with a maximum precision of 38 digits.
It is important to keep in mind the precision and scale ranges while defining the data type and to use the NUMERIC and DEC synonyms, where necessary. Creating tables with DECIMAL and NUMERIC columns and querying data from them is easy in SQL Server.
Finally, the DECIMAL data type allows us to restrict values to a fixed precision and scale, thus increasing the accuracy of our data. In summary, the SQL Server DECIMAL data type is an essential tool in storing fixed-point decimal values with a maximum precision of 38 digits.
Its precision and scale ranges enable accurate data storage and retrieval that increase the overall accuracy of our data. The example clearly demonstrates how it is easy to create tables with DECIMAL and NUMERIC columns and query data from them.
By using synonyms such as NUMERIC and DEC, we can improve the readability of our code. Finally, we must keep in mind the precision and scale ranges while defining the data type and use the DECIMAL data type to restrict values to a fixed precision and scale, ensuring that our data is as accurate as possible.