SQL DECIMAL and Precision

Introduction

SQL applications often need to store values that contain fractional digits but must remain exact: prices, balances, salaries, tax amounts, interest rates, percentages, exchange rates, quantities, and regulated measurements. The DECIMAL and NUMERIC types are designed for this work. They represent decimal values with a declared number of total digits and fractional digits, allowing a schema to express both storage capacity and numerical intent.

The familiar declaration DECIMAL(10,2) contains two independent design decisions. Ten is the precision, which limits the total number of digits, and two is the scale, which describes digits to the right of the decimal point. Understanding that relationship is essential because a type can reject a large integer part, round an overly detailed fraction, or produce a wider result during arithmetic. Correct decimal design therefore covers more than choosing two places for money.

What Is DECIMAL?

DECIMAL is an exact numeric SQL data type. Within its supported precision and scale, the database stores decimal values without the binary representation differences associated with floating-point types. A value such as 19.99 can remain exactly 19.99 rather than the nearest available binary fraction.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    price DECIMAL(10,2) NOT NULL
);

Exact storage does not mean unlimited capacity or unlimited fractional detail. The declaration defines a finite domain, and operations can still require rounding or fail with overflow.

DECIMAL and NUMERIC

NUMERIC belongs to the same exact decimal family. In the major relational databases, DECIMAL(p,s) and NUMERIC(p,s) are usually equivalent or implemented as synonyms. SQL-standard wording permits a subtle distinction in minimum guarantees, but ordinary schema design treats them alike unless product documentation states otherwise.

Teams should select one spelling as a convention and use it consistently. Consistency improves readability, but it does not remove the need to verify maximum supported precision, unconstrained behavior, arithmetic rules, and driver mappings for the chosen database.

Understanding DECIMAL(p,s)

The general declaration is DECIMAL(precision, scale), commonly abbreviated as DECIMAL(p,s). Precision is the maximum total number of represented decimal digits. Scale is the number of fractional digits represented to the right of the decimal point.

DECIMAL(10,2)

precision = 10 total digits
scale     =  2 fractional digits
capacity  =  8 integer digits + 2 fractional digits

The decimal point and a negative sign do not consume precision. Both 123.45 and -123.45 contain five numeric digits and have a scale of two.

Precision and Scale Formula

The number of available digits before the decimal point is usually calculated as p - s. For DECIMAL(10,2), eight digits remain for the integer portion. Its typical range is from -99,999,999.99 through 99,999,999.99.

DeclarationInteger digitsFractional digitsTypical maximum
DECIMAL(5,2)32999.99
DECIMAL(6,2)429,999.99
DECIMAL(8,3)5399,999.999
DECIMAL(12,4)8499,999,999.9999

Increasing scale while keeping precision fixed reduces integer capacity. Changing DECIMAL(10,2) to DECIMAL(10,5) does not simply add detail; it reduces the integer portion from eight digits to five.

Why Exact Decimal Types Exist

People and business rules commonly express values in base ten. A contract may require an invoice total to match its printed decimal amount exactly, a ledger must reconcile to the smallest supported currency unit, and a tax calculation must follow a documented rounding rule. Approximate binary types are poorly matched to these requirements because many ordinary decimal fractions cannot be represented exactly in binary.

Exact decimal types preserve declared decimal values and support equality, grouping, and reconciliation based on those values. They are therefore the default choice when a one-cent, one-basis-point, or one-unit difference has business meaning.

DECIMAL Versus Floating Point

ConcernDECIMAL or NUMERICFLOAT or DOUBLE
RepresentationExact decimal within declared limitsApproximate binary representation
Best fitMoney, rates, contractual quantitiesScientific data, simulation, analytics
Capacity modelPrecision and scaleSignificant digits and exponent
EqualitySuitable for exact stored valuesCalculated values often need tolerance
RangeExplicitly bounded by precisionUsually much wider magnitude range

Neither family is universally better. The correct choice follows the domain. A product price should generally be decimal; a noisy temperature reading may reasonably be floating point. Using decimal for every scientific calculation can be inefficient, while using floating point for every fractional value can violate exact business rules.

Money and Currency Design

A common price column is DECIMAL(19,4) or another organization-approved range. The extra fractional digits can support unit prices, prorated charges, and intermediate calculations even when displayed totals use two places. The final type must be selected from actual maximum amounts, currencies, regulations, and calculation rules rather than copied blindly.

CREATE TABLE account_balances (
    account_id BIGINT PRIMARY KEY,
    currency_code CHAR(3) NOT NULL,
    balance DECIMAL(19,4) NOT NULL,
    CHECK (balance >= 0)
);

An amount without a currency is incomplete in multi-currency systems. Currency metadata determines valid scale, rounding, and interpretation. Not every currency uses two minor-unit digits, and assets such as cryptocurrencies may require substantially more.

Rates and Percentages

Percentages require a representation convention. Fifteen percent can be stored as 15.0000 or as the fraction 0.150000. Both work, but mixing them creates hundredfold errors. The column name, documentation, constraints, and formulas must communicate the selected convention.

discount_rate DECIMAL(7,6)
    CHECK (discount_rate BETWEEN 0 AND 1)

tax_percent DECIMAL(7,4)
    CHECK (tax_percent BETWEEN 0 AND 100)

A declaration alone controls representable digits, not business meaning. A CHECK constraint turns domain rules such as nonnegative values or a maximum percentage into enforceable database behavior.

Precision Is Not a Business Constraint

DECIMAL(10,2) permits negative amounts and values up to nearly one hundred million. If a product price must fall between zero and one million, precision does not express the complete rule. Use NOT NULL, CHECK, keys, and related constraints alongside the numeric type.

price DECIMAL(10,2) NOT NULL
    CHECK (price BETWEEN 0.00 AND 1000000.00)

Defaults should also reflect a real business default. Automatically assigning 0.00 can hide missing data when zero and unknown have different meanings.

Values With Excess Fractional Digits

When a value has more fractional digits than the target scale, the database commonly rounds it to the supported scale. Exact behavior can depend on the product, expression, conversion, and session settings. Inserting 12.345 into DECIMAL(5,2) may produce 12.35, but applications should not rely on accidental implicit rounding for financial policy.

Apply the approved rule explicitly at the correct business boundary and test halfway values, negative values, and product-specific rounding modes. Truncation simply removes extra digits, while rounding may change the retained digit; they are not interchangeable.

Overflow and Excess Integer Digits

Scale reduction may allow a value to fit after rounding, but too many integer digits usually cause numeric overflow. For example, 1000.00 cannot fit into DECIMAL(5,2), whose integer capacity is only three digits. Applications should validate expected ranges and handle database errors rather than silently clipping values.

Overflow can also occur in expressions even when every source column accepts its own value. Multiplication, division, summation, and casts can create results wider than the original columns.

Planning Precision and Scale

Begin with the largest credible absolute value, not merely today's sample data. Count the required integer digits, select fractional resolution, and add them to obtain minimum precision. Then consider growth, imported data, intermediate arithmetic, and product limits.

required precision = integer digits + fractional digits

maximum amount: 25,000,000.00
integer digits: 8
fractional digits: 2
minimum declaration: DECIMAL(10,2)

A small safety margin often prevents avoidable migrations, but extreme precision should not be selected without reason. Wider values can increase storage, index width, memory use, and arithmetic cost, and they may not map cleanly through every system.

DECIMAL Arithmetic

Decimal arithmetic remains exact within the precision and scale chosen for each result. Addition and subtraction generally need enough scale for both operands and enough integer capacity for a carry. Multiplication combines fractional detail and can increase total digits substantially. Division often needs a result scale because many quotients, such as one divided by three, have no finite decimal representation.

SELECT unit_price * quantity AS line_amount,
       subtotal * tax_rate AS tax_amount,
       total_amount / installment_count AS installment_amount
FROM invoice_lines;

The result type is not necessarily identical to either input. Each database has formulas for deriving expression precision and scale and may reduce them when the result exceeds its maximum supported precision.

Result Precision and Scale

Arithmetic rules matter most in chained calculations. Multiplying DECIMAL(12,4) by DECIMAL(12,4) may yield a result with eight fractional digits and greater total precision. Division may allocate additional fractional digits. Later assignment to a narrower column can round or overflow.

Review the actual result metadata for critical expressions. Do not assume that because operands are decimal, every intermediate automatically has the desired business scale. Product documentation and small metadata tests are more reliable than intuition.

Explicit Casting and Rounding

An explicit CAST documents the expected result and avoids dependence on an implicit conversion chosen by the engine. It is especially useful when combining integer, decimal, and floating operands or when an expression crosses a reporting or posting boundary.

SELECT CAST(quantity * unit_price AS DECIMAL(19,4)) AS line_amount,
       ROUND(subtotal * tax_rate, 2) AS posted_tax
FROM invoices;

Rounding should occur when the business process requires it, not after every operator. Premature rounding can accumulate error across many line items. Retain sufficient intermediate precision, then apply the mandated rule to the value that is actually posted, billed, or displayed.

Exact Does Not Mean Infinite

The word exact means a stored decimal value can be represented exactly within its type. It does not mean every mathematical result is finite or every calculation fits. One third has a repeating decimal expansion, a large multiplication can exceed maximum precision, and division by zero remains invalid.

Exactness must therefore be paired with finite capacity, explicit result scale, overflow handling, and a rounding policy. This distinction prevents the common misconception that changing a column from float to decimal makes all numerical concerns disappear.

Equality, Filtering, and Sorting

Exact decimal columns support ordinary equality predicates for values stored at the same intended scale. Range filters and sorting also follow numeric order, unlike text storage. Parameter types should match the column so the database does not need to convert the indexed column during comparison.

SELECT product_id, price
FROM products
WHERE price BETWEEN CAST(10.00 AS DECIMAL(10,2))
                AND CAST(50.00 AS DECIMAL(10,2))
ORDER BY price;

Different scales can still represent equal numeric values, such as 1.0 and 1.00, although display formatting and scale metadata may differ by product or client.

Why Numbers Should Not Be Stored as Text

A VARCHAR price permits invalid strings, complicates arithmetic, and sorts lexically, where 100 can appear before 20. Every query then needs parsing and error handling, while indexes reflect textual rather than numeric semantics.

Store the value using the numeric type that represents its domain. Formatting symbols, currency labels, grouping separators, and localized decimal separators belong in the presentation layer.

Aggregates and Grouping

SUM, AVG, MIN, and MAX work naturally with decimal columns, but aggregate result types vary by database. A sum across millions of rows may need more integer capacity than one row. An average introduces division and may use an expanded scale.

GROUP BY and DISTINCT operate on exact numeric values, making decimals more predictable than approximate calculations for reconciliation. Still, reports should cast or round at a defined boundary rather than depending on a client's display defaults.

NULL, Zero, and Defaults

NULL means a value is absent or unknown; it is not the same as 0.00. Aggregates generally ignore NULL values, while arithmetic involving NULL commonly returns NULL. Converting missing amounts to zero can change averages, balances, and business meaning.

Use NOT NULL when every row must have an amount. Use a default only when zero or another value is genuinely correct for a newly created row, not as a substitute for complete input.

Derived Values Versus Stored Totals

An invoice total can be calculated from its lines or stored as a posted business fact. Calculated values avoid duplication, while stored totals can preserve the exact amount approved at a particular time under a particular tax and rounding policy. The right approach depends on audit, immutability, performance, and correction requirements.

If both detail and total are stored, define which is authoritative and validate their relationship. Keep calculation version, currency, and rounding policy where historical reproduction matters.

DECIMAL for Measurements

Decimal is appropriate when a measurement is recorded or regulated at a fixed decimal resolution, such as a packaged weight to three places or a laboratory result reported to a defined decimal increment. Floating point may be better for broad-range scientific computation, sensor streams, simulation, and statistical processing where approximation is inherent.

Precision is not accuracy. A column that stores six fractional digits does not make an instrument accurate to six places. Database scale preserves reported detail; calibration and measurement uncertainty determine accuracy.

Database-Specific Behavior

MySQL supports fixed-point DECIMAL with product-defined maximum precision and storage rules. PostgreSQL NUMERIC supports declared precision and scale as well as unconstrained numeric values, which preserve input detail but do not enforce a uniform scale. SQL Server treats DECIMAL and NUMERIC as synonyms and derives expression metadata through documented formulas.

Oracle commonly uses NUMBER(p,s), with semantics and flexibility that require Oracle-specific review. SQLite uses dynamic type affinity; declaring DECIMAL does not provide the same fixed decimal enforcement found in traditional strongly typed engines. Portable designs must test behavior rather than rely on a shared type name.

DECIMAL Without Precision and Scale

The meaning of an unconstrained DECIMAL or NUMERIC declaration varies. One product may supply a default precision and scale, another may accept arbitrary precision within implementation limits, and a migration tool may choose its own mapping.

Explicit DECIMAL(p,s) is usually clearer for business fields because it makes capacity and validation visible. Unconstrained numeric storage can be useful for general mathematical data, but it should be an intentional decision.

Application Mapping

Java should normally map exact SQL decimals to BigDecimal, not double. Python's Decimal serves the same purpose when configured and used consistently. Other languages need an arbitrary-precision or decimal library that preserves the database value and its required operations.

BigDecimal price = resultSet.getBigDecimal("price");
BigDecimal total = price.multiply(quantity);
BigDecimal posted = total.setScale(2, RoundingMode.HALF_UP);

Construct decimal objects from strings or exact inputs when the language API recommends it. Creating a decimal from an already approximate binary number can preserve the approximation rather than the intended source value.

JavaScript and Browser Clients

JavaScript's standard Number is binary floating point. Parsing a high-precision database decimal into a Number can lose digits even though the database stored them exactly. APIs often serialize important decimal values as JSON strings, then use a decimal library or validated text handling in the client.

The contract must say whether a field is a JSON number or string, what scale is expected, and how clients should round. A browser that only displays a value may safely retain the canonical string, while a client performing financial arithmetic needs decimal-aware operations.

DECIMAL in APIs and Serialization

Precision must survive the entire path: database, driver, service model, serializer, network payload, client parser, and any return trip. Database correctness is lost if an ORM maps the column to a binary float or a serializer emits fewer significant digits.

{
  "currency": "USD",
  "amount": "123456789012345.6700"
}

A string representation is not mandatory for every API, but it is a common way to avoid consumer limits. Schema documentation should define pattern, maximum digits, scale, currency, and examples.

ORM and Parameter Binding

ORM models should declare precision and scale explicitly rather than depending on framework defaults. Schema generation can otherwise create a narrower column, and later migrations may repeatedly detect false differences. Validate the actual generated database definition.

Bind decimal parameters with the driver's decimal type. Passing text or floating values can introduce parsing, approximation, implicit conversion, or poor index use. When precision is critical, integration tests should read back exact boundary values through the same driver used in production.

Conversions Between Numeric Types

Integer-to-decimal conversion is exact when the destination has enough integer capacity. Decimal-to-integer conversion discards or rounds fractional data according to product behavior. Float-to-decimal conversion may expose the approximate binary value, while decimal-to-float can lose decimal exactness and significant digits.

SELECT CAST(order_count AS DECIMAL(12,0)),
       CAST(measured_value AS DECIMAL(18,6)),
       CAST(amount AS DOUBLE PRECISION)
FROM metrics;

Every conversion should state why loss is acceptable and where rounding occurs. Converting money to float solely for convenience is a design warning.

Indexes and Performance

Decimal columns can be indexed for equality, range, sorting, and grouping. Wider precision can require more storage and produce larger indexes, which affects cache use and write cost. Exact arithmetic may also be slower than native floating arithmetic in calculation-heavy workloads.

These costs rarely justify an incorrect type. Choose the narrowest range that safely supports the domain, index predicates used by real queries, and measure with representative data. Avoid wrapping indexed columns in conversion functions when a correctly typed parameter can be used.

Integer Minor Units Versus DECIMAL

Some systems store money as integer minor units, such as cents. Integers provide simple exact arithmetic and broad language support, but the model must know each currency's minor-unit convention and handle values requiring sub-minor precision. Exchange rates and prorated prices still need another representation.

DECIMAL expresses human-readable values and variable scale directly. Integer minor units can be effective in a carefully designed payment domain, while decimal is often clearer for SQL reporting and mixed monetary calculations. The important requirement is one documented representation, not a universal preference.

Financial Calculation Strategy

Financial formulas should define input scale, intermediate scale, rounding mode, and posting point. For example, a system may multiply quantity by a four-place unit price, retain six places while calculating tax, round each posted line to currency scale, then sum posted lines. Another jurisdiction may require tax to be calculated on the invoice total.

Those policies produce different legitimate results. They must be captured in requirements and tests, not inferred from a column declaration. Store audit inputs and posted outputs when results must be reproduced later.

Schema Examples

CREATE TABLE invoice_lines (
    invoice_id BIGINT NOT NULL,
    line_number INTEGER NOT NULL,
    quantity DECIMAL(14,4) NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(19,4) NOT NULL CHECK (unit_price >= 0),
    tax_rate DECIMAL(9,8) NOT NULL CHECK (tax_rate BETWEEN 0 AND 1),
    posted_total DECIMAL(19,2) NOT NULL,
    PRIMARY KEY (invoice_id, line_number)
);

This example separates quantity, price, rate, and posted total because they have different ranges and scale requirements. One universal decimal declaration would hide those distinctions.

Calculation Ownership and Consistency

A system can preserve every decimal digit and still produce inconsistent results when the same formula is implemented differently in SQL, backend services, browser code, spreadsheets, and reporting tools. Each implementation may use a different intermediate scale, order of operations, or rounding mode. Decide which layer owns an authoritative calculation and publish the rule for every other consumer. Shared test vectors are especially useful: the same inputs, expected intermediate values, and posted result can be executed by the database, service, and reporting pipeline.

Moving a calculation into SQL does not automatically make it authoritative. The query must use correctly typed constants and parameters, and its result must be cast at the documented boundary. Conversely, performing a calculation in an application requires a decimal-capable type and an explicit rounding mode. Avoid allowing a database default, ORM default, or user-interface formatter to become an undocumented accounting policy.

Version formulas when their rules can change. A historic invoice may need the tax method and rounding sequence that were valid when it was posted, not the method currently used for new invoices. Storing the rule version and authoritative outputs makes later audit and correction possible without rewriting history.

Reconciliation and Operational Controls

Decimal fields should participate in data-quality monitoring just like dates, statuses, and identifiers. Useful checks include values outside expected business ranges, unexpected scale, failed conversions, totals that do not reconcile with details, and sudden distribution changes. A column may technically accept 99999999.99 while that value is impossible for the product, so database capacity and operational alert thresholds should remain separate controls.

Reconciliation must compare values at the correct stage. A high-precision calculated tax may legitimately differ from its two-place posted tax, while the ledger should match the posted value exactly. Reports should identify which amount they use and should not silently recompute historic totals with newer rules. When a difference is expected because of allocation or rounding, store or report the adjustment explicitly rather than hiding it in an unrelated line.

Import pipelines should count rejected, rounded, defaulted, and out-of-range records. Record source text before destructive normalization when traceability is required. If an upstream file changes from two to four decimal places, monitoring should expose that contract change before values are silently reduced to the target scale.

Display Scale Versus Stored Scale

Stored scale describes numerical capacity, while display scale is a presentation decision. A rate stored with eight fractional digits may appear as a percentage with four places, and a unit price stored with four places may appear as a two-place line total. Formatting a value does not change the stored value unless the formatted result is parsed and written back.

Keep formatting rules in the presentation or reporting contract and calculation rules in the domain contract. This separation allows a user interface to localize decimal separators and currency symbols without changing database precision. It also prevents a display preference from causing irreversible rounding in later calculations.

Migration and Schema Changes

Increasing precision without changing scale is often straightforward, but reducing precision or scale can round values or reject rows. Before a migration, profile maximum integer digits, fractional digits, negative values, NULLs, and values outside the proposed business range.

A float-to-decimal migration also requires a rounding policy because existing values may contain approximation residue. Update application mappings and API contracts with the database change, test aggregates before and after, and retain a rollback or reconciliation plan.

Common DECIMAL Mistakes

  • Confusing precision with the number of integer digits.
  • Forgetting that increasing scale reduces integer capacity when precision stays fixed.
  • Using DECIMAL(2,2) for percentages stored from zero through one hundred.
  • Assuming all currencies require exactly two fractional digits.
  • Choosing scale from display formatting rather than calculation requirements.
  • Rounding every intermediate result and accumulating avoidable differences.
  • Assuming exact decimal arithmetic has unlimited range or division precision.
  • Mapping a decimal column to double in application code.
  • Using decimal for identifiers that should be integer or character keys.
  • Storing formatted numeric values in text columns.
  • Ignoring product-specific arithmetic and unconstrained numeric behavior.
  • Expecting precision and scale to enforce every business rule.

Precision Planning Checklist

  1. Determine the largest credible positive and negative values.
  2. Count the required integer digits.
  3. Define required input and stored fractional resolution.
  4. Add integer digits and scale to obtain minimum precision.
  5. Allow justified capacity for growth and imports.
  6. Analyze multiplication, division, sums, and averages.
  7. Document rounding and truncation rules.
  8. Add NULL, range, and domain constraints.
  9. Verify DBMS limits and expression result rules.
  10. Test driver, ORM, JSON, and client mappings.

Testing Decimal Columns

Test zero, negatives, maximum and minimum valid values, one unit beyond each boundary, and values with fewer and more fractional digits than the scale. Include halfway rounding values, large aggregates, division, multiplication, NULL behavior, and conversion from every external source.

End-to-end tests should insert through the application, query through the production driver, serialize through the API, parse in the client, and return the value where applicable. Compare canonical values and posted results exactly, including trailing-scale expectations when those are part of the contract.

Interview Questions and Answers

What does DECIMAL(10,2) mean? It allows ten total digits, with two fractional digits and normally eight digits available before the decimal point.

Do the sign and decimal point count toward precision? No. Precision counts numeric digits.

Why is DECIMAL preferred for money? It preserves decimal values exactly within declared limits and supports deterministic decimal reconciliation.

Can DECIMAL round a value? Yes. Assignment to a smaller scale and expression result rules can require rounding.

Is DECIMAL always better than FLOAT? No. Floating point is suitable for approximate scientific and analytical data with wide magnitude ranges.

Why can decimal arithmetic overflow? Exactness does not remove finite precision; an expression can produce more integer or fractional digits than its result type supports.

How should Java map an SQL decimal? Normally to BigDecimal, preserving an explicit scale and rounding mode where needed.

Key Takeaway

DECIMAL and NUMERIC are exact numeric types for values whose decimal digits carry business meaning. Precision controls total digits, scale controls fractional digits, and their difference determines normal integer capacity.

DECIMAL DESIGN = REQUIRED INTEGER RANGE
               + REQUIRED FRACTIONAL SCALE
               + ARITHMETIC GROWTH
               + ROUNDING POLICY
               + END-TO-END TYPE PRESERVATION

Select the type from domain requirements, reinforce it with constraints, review expression result types, and preserve exactness across drivers and APIs. A well-designed decimal column does more than hold a number: it documents the range, resolution, and calculation expectations of the system.