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. 

DECIMAL(precision, scale

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. 


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.

The decimal data type

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
  FROM Production.Product 
  where [weight] is not null

Additional Resources 

To learn more about SQL numeric types, check out these useful resources: 

More from the blog

MySQL PostgreSQL SQLite SqlServer