# 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.

## 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:

Precision | Storage Size (in bytes) |
---|---|

1-9 | 5 |

10-19 | 9 |

20-28 | 13 |

29-38 | 17 |

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.