Decimal and Numeric Types

The DECIMAL and NUMERIC data types are functionally identical in SQL Server. They are fixed-precision and scale data types.

Syntax

Both DECIMAL and NUMERIC can be declared with an optional precision and scale.

DECIMAL [ ( p [ , s ] ) ]
NUMERIC [ ( p [ , s ] ) ]

p (precision): The total number of digits that can be stored, both to the left and right of the decimal point. The maximum precision is 38. If not specified, the default is 18.
s (scale): The number of digits to the right of the decimal point. The maximum scale is equal to the precision. If not specified, the default is 0.

Description

DECIMAL and NUMERIC are used to store numbers with a fixed number of decimal places. They are suitable for financial calculations and other applications where exact precision is critical. Unlike floating-point types (FLOAT and REAL), these types do not store approximations of numbers; they store the exact values.

When you declare a column with DECIMAL(p, s) or NUMERIC(p, s), SQL Server allocates the necessary storage to hold numbers with exactly p digits and s digits after the decimal point.

Example Usage

Let's consider some examples:

DECIMAL(10, 2): This can store numbers with a total of 10 digits, with 2 digits after the decimal point. For example, 12345678.90 is valid, but 123456789.01 would exceed the precision and cause an error.
NUMERIC(5): This is equivalent to NUMERIC(5, 0). It can store numbers with a total of 5 digits, with no digits after the decimal point. For example, 12345 is valid.
DECIMAL (without precision and scale): This defaults to DECIMAL(18, 0).

Storage Requirements

The storage required for DECIMAL and NUMERIC depends on the precision (p) and scale (s) specified. SQL Server uses a variable number of bytes to store these values. The general rule of thumb is that every 9 digits of precision require 4 bytes of storage.

Precision (p)	Storage (Bytes)
1-9	5
10-19	9
20-28	13
29-38	17

When to Use Decimal/Numeric

Use DECIMAL or NUMERIC when:

You need to store exact values, especially for financial transactions.
You need to control the number of decimal places displayed.
You are performing calculations where rounding errors from floating-point types are unacceptable.

Important Note: While DECIMAL and NUMERIC offer exact precision, they can be more computationally expensive than floating-point types for certain operations. However, for accuracy-critical applications, the overhead is usually a worthwhile trade-off.

Comparison with FLOAT and REAL

FLOAT and REAL are approximate-numeric data types. They store values that are close to the actual value, but not necessarily the exact value. This is because they use a binary representation, which cannot always precisely represent decimal fractions.

For example, a FLOAT type might store 0.1 as a value very close to, but not exactly, 0.1. This can lead to small inaccuracies accumulating over many calculations.

Choose DECIMAL/NUMERIC for exactness.

Choose FLOAT/REAL for speed and when approximate values are acceptable.