DECIMAL (SQL)

DECIMAL(p, s) in SQL stores fixed-precision decimal numbers exactly, where $p$ is the total number of digits and $s$ is the number of digits after the decimal point. DECIMAL(5, 3) can store numbers from $-99.999$ to $99.999$. DECIMAL(10, 2) can store amounts up to about $\pm 99,999,999.99$ — appropriate for dollar amounts up to about $100 million with cent precision.

The defining property of DECIMAL is exactness. The number $0.1$ is exactly $0.1$, not the approximate $0.1000000000000000055\dots$ that you’d get from a binary floating-point representation. That makes DECIMAL the correct type for any application where small rounding errors compound into wrong totals — the canonical case being financial data.

CREATE TABLE invoices (
    invoice_id  INT NOT NULL AUTO_INCREMENT,
    amount      DECIMAL(10, 2),
    tax_rate    DECIMAL(5, 4),
    PRIMARY KEY (invoice_id)
);

Arithmetic on DECIMAL values is slower than arithmetic on integers or floats, because the database has to maintain decimal precision rather than letting binary floating-point hardware do the work. For most application code that cost is negligible compared to the cost of getting the wrong answer.

The alternative — FLOAT or DOUBLE — stores approximate floating-point values the way C and Python do. Floats are fast, cover a huge range (single-precision goes to $\pm 3.4\times 10^{38}$, double-precision to $\pm 1.8\times 10^{308}$), and lose precision in the last digit or two. They’re appropriate for scientific calculations and sensor readings, where speed and dynamic range matter and a few bits of precision in the trailing digits don’t. They are not appropriate for money. 0.1 + 0.2 == 0.30000000000000004 in double-precision float is fine in a physics simulation and unacceptable in an invoice line.

DECIMAL is part of the SQL numeric types, alongside the integer types (TINYINT/SMALLINT/INT/BIGINT for varying widths) and the approximate floating-point types (FLOAT and DOUBLE).