Numeric vs. Decimal Data Types in SQL

In standard SQL, there is a slight difference between the NUMERIC and DECIMAL data types. However, in some variants of SQL, there is no difference! This article will clear up any confusion.

In this article, I’ll explain the differences and similarities between SQL’s standard NUMERIC and DECIMAL data types. Then I’ll discuss how these data types work in common SQL dialects. We’ve already posted a more general article on SQL data types; this time, we’ll drill down further on just the DECIMAL and NUMERIC data types.

If you're looking for a comprehensive SQL course, I recommend the interactive SQL from A to Z learning path. It contains over 800 exercises and covers the basic, intermediate, and advanced topics of SQL. Learn all modern SQL in one place!

SQL DECIMAL vs. NUMERIC Data Types

There are two different data types that allow us to store precise numerical data in SQL: NUMERIC and DECIMAL.

Both of these types store decimal numbers and use exact arithmetic – as opposed to floating point numbers (REAL, FLOAT, etc.), which store binary numbers and use inexact arithmetic. Thus, DECIMAL or NUMERIC should be used for money values, whereas floating point numbers should be used for scientific calculations.

Both NUMERIC and DECIMAL take the arguments of precision and scale. Precision is the maximum total number of digits that a number can have. Scale is the number of digits after the decimal point. For the number 10293.93, the precision is 7 and the scale is 2.

There is one notable difference between NUMERIC and DECIMAL in standard SQL. The NUMERIC data type is strict; it enforces the exact precision and scale that you have specified. This is in stark contrast to DECIMAL, which allows more numbers than the stated precision.  Let’s dive into the syntax of both data types to explore this further.

NUMERIC Syntax and Definition

NUMERIC(p, s) takes two arguments: precision (p) and scale (s). The range of values this type can store is -10^38+1 to 10^38. The storage size for each precision range is shown below:

The syntax is ...

NUMERIC(p, s) 

… where:

• p = precision, or the maximum total number of digits to be stored (including both sides of the decimal point). This value must be between 1 and 38. The default value for p is 18.
• s = scale, or the number of digits to the right of the decimal point. It can be specified only when the precision (p) is specified. The default is 0.

You can declare NUMERIC like so:

DECLARE @MyNumeric NUMERIC(8, 4) 

Here, the precision is 8 total digits, with 4 to the right of the decimal point.

DECIMAL Syntax and Definition

DECIMAL(p, s) takes the same two arguments. However, with the DECIMAL data type, the precision can be greater than the value you have supplied. Thus, this data type can provide you with more flexibility. The range of values is: -10^38+1 to 10^38. The storage size for each precision range is the same as for the NUMERIC data type.

The syntax is ...

DECIMAL(p, s) 

… where:

• p = precision. It’s similar to the precision specified for NUMERIC, but the number of digits can be actually greater than p.
• s = scale, exactly like the scale specified for NUMERIC. There can be a maximum of s digits to the right of the decimal point.

You can declare a decimal value like so:

DECLARE @MyDecimal DECIMAL(8, 4)

Once again, we have 8 total digits, with 4 to the right of the decimal point. In Standard SQL you can store more than 8 digits there if you want (but only 4 to the right of the decimal point).

If you’re still feeling unsure about these concepts, check out this post that explains SQL’s numeric data types. It will show you more examples.

DECIMAL vs. NUMERIC in Common SQL Dialects

Let’s look at the difference between DECIMAL and NUMERIC for some of the main SQL dialects. If you’re not familiar with them, check out this article explaining SQL dialects in detail.

Oracle

Oracle has the DECIMAL and NUMBER (not NUMERIC) types. They are the same, but NUMBER is generally used.

PostgreSQL

DECIMAL and NUMERIC are the same in Postgres; the precision in DECIMAL can't be greater than specified. (In other words, DECIMAL is treated exactly like NUMERIC). You can learn more about PostgreSQL data types here.

T-SQL

In T-SQL, DECIMAL and NUMERIC are functionally equal.

MySQL

NUMERIC is implemented as DECIMAL; there is no difference to worry about.

SQLite

DECIMAL is treated as NUMERIC. Here, NUMERIC is not a data type, but an affinity; affinities help improve the compatibility between SQLite and other databases.

Firebird

In Firebird, there is a difference in how NUMERIC and DECIMAL behave:

• “The NUMERIC definition means exactly the precision requested (total number of digits).
• DECIMAL will have at least the requested precision, but it may have more. (The digits to the right of the decimal symbol, however, are maintained exactly.)” [Source: Firebird Documentation]

Want to Learn More About SQL Data Types?

We have covered the differences and similarities of the NUMERIC and DECIMAL data types for the SQL standard and the most common dialects of SQL. Data types as a whole are covered in our SQL A-Z Learning Track. If this article interests you, consider checking out the course for a more complete education on the topic.