Use the SQL Server DECIMAL data type to define columns that have fixed precision and scale. Unlike FLOAT, DECIMAL data type has fixed decimal places. Use DECIMAL data type and NUMERIC data type keywords interchangeably to define the type.
When defining, the DECIMAL data type provides both precision and scale.
The precision defines the total number of decimal digits to store within the number. This includes digits to the left and right of the decimal.
The scale defines the total number of decimal digits to the right of the decimal. The scale is a value between 0 and precision.
Subtract scale from precision to find the number of digits to the left of the decimal point.
SQL SERVER DECIMAL Data Type Example
Confused? Check out this diagram, it helps!
As you see, DECIMAL(10,4) defines a number containing a total of ten digits, with four to the right of the decimal place. It follows there are six to the left.
Examples using DECIMAL
Let’s look at an example using SQL Server DECIMAL data type within the Production.Product table. We’ll work with the Weight column.
Notice its type is DECIMAL(8,2), meaning there are two decimals within a total of eight digits. It is capable of storing this number: 123456.21, but not 7654321.12
You query this type like any other numeric field. Here is how:
SELECT ProductID ,[Name] ,[Weight] FROM Production.Product where [weight] is not null
To learn more about SQL numeric types, check out these useful resources:
- Common Data Types used in SQL Server
- How do I handle a “Error converting data type” error?
- Recommended book – SQL Queries for Mere Mortals