Significant Figures in SQL: How to Round and Store Numeric Data in Databases

Rule Statement

In SQL, significant figures are not a native column property. The SQL standard defines exact numeric types (DECIMAL, NUMERIC) with precision and scale, and approximate numeric types (FLOAT, REAL, DOUBLE PRECISION) with binary floating-point representation. The ROUND function almost always takes a decimal-place argument, not a significant-figure count. Therefore, the governing rule for databases is: store measured values at the precision supported by the measurement and the column type, and apply significant-figure rounding at display, reporting, or specification-conformance boundaries.

This aligns with ISO 80000-1:2009, clause 7.3, which treats rounding as a presentation and recording operation; ASTM E29-13, which uses significant digits to determine conformance with specifications; and the GUM (JCGM 100:2008), clause 7.2, which recommends rounding uncertainty to one or two significant digits and matching the measurement result to that place. For SQL implementation, use DECIMAL(p,s) when exact decimal behavior is required, and reserve FLOAT/REAL for approximate scientific computation.

Worked Examples

Example 1: Round 12345 to 3 significant figures

1. Find order of magnitude: FLOOR(LOG10(ABS(12345))) = 4.
2. Compute shift: order - (sig_figs - 1) = 4 - 2 = 2.
3. Scale, round, unscale: ROUND(12345 / 10^2) * 10^2 = ROUND(123.45) * 100 = 123 * 100 = 12300.

SQL pattern:

WITH v(x, sf) AS (VALUES (12345.0, 3))
SELECT ROUND(x / POWER(10, FLOOR(LOG10(ABS(x))) - (sf - 1)))
       * POWER(10, FLOOR(LOG10(ABS(x))) - (sf - 1)) AS rounded
FROM v;

The result is 12300, not 12345.00. In a DECIMAL column, trailing zeros may be preserved only if the column scale and display formatting preserve them.

Example 2: Round 0.004567 to 2 significant figures

1. FLOOR(LOG10(ABS(0.004567))) = -3.
2. Shift: -3 - (2 - 1) = -4.
3. ROUND(0.004567 / 10^-4) * 10^-4 = ROUND(45.67) * 0.0001 = 46 * 0.0001 = 0.0046.

This is the correct significant-figure result. A plain ROUND(0.004567, 2) would return 0.00, which is a decimal-place operation and is not equivalent.

Example 3: Storage versus display

If a sensor reports 12.3456 V with uncertainty ±0.01 V, the measurement is meaningful to the hundredths place. Store DECIMAL(10,4) if the raw instrument output must be retained, but display and report as 12.35 V after uncertainty rounding. Do not store only 12.35 unless the audit trail and uncertainty metadata are also stored.

Counter-Examples

• Decimal places mistaken for significant figures. ROUND(12345.67, 2) returns 12345.67 in many systems, not 12000 or 12300.
• Using FLOAT for exact decimal measurements. Binary floating point cannot represent many decimal fractions exactly. 0.1 + 0.2 may produce 0.30000000000000004, corrupting significant-figure comparisons.
• Rounding before aggregation. Summing pre-rounded values accumulates bias and can violate the intent of ASTM E29 conformance testing.
• Double rounding. Rounding 1.245 to 2 significant figures and then to 1 significant figure can differ from direct 1-significant-figure rounding. See Double Rounding Error.
• Ignoring trailing zeros. In significant-figure notation, 1200 may have two, three, or four significant figures. In SQL, DECIMAL(6,2) stores 1200.00, but the displayed zeros may be decimal-place zeros, not significant digits.

Convention Comparison Table

DBMS behavior differs: SQL Server ROUND accepts a third argument to truncate; PostgreSQL offers round(numeric, int) and round(double precision); MySQL and Oracle use ROUND(value, decimals); SQLite provides round(X,Y). Always test the exact version and type.

Standards Citation

ASTM E29-13, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, provides the standard practice for rounding test data to significant digits before comparing with specification limits.

ISO 80000-1:2009, Quantities and units — Part 1: General, clause 7.3, gives rules for rounding numbers and expressing numerical values.

JCGM 100:2008 (GUM), clause 7.2, recommends rounding the uncertainty to one or two significant digits and rounding the measurement result to the same decimal place.

NIST TN 1297, Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results, Section 4.6, gives practical rounding guidance consistent with the GUM.

Common Mistakes

• Assuming every database has a SIGNIFICANT_DIGITS function. Most do not; you must compute it with LOG10 and POWER or in application code.
• Storing rounded values and discarding raw measurements, making later uncertainty analysis impossible.
• Using DECIMAL(5,2) for a value such as 12345.67; precision 5 means at most 5 total digits, so the insert overflows.
• Forgetting that DECIMAL(p,s) scale is fixed. It does not dynamically track significant figures.
• Comparing rounded values with exact equality (=) instead of tolerance intervals. See Tolerance Intervals.
• Mixing exact and approximate types in one expression; the result may become approximate and lose decimal exactness.

Software Behavior Note

SQL engines implement rounding differently at the edges. For exact NUMERIC types, many engines use round-half-away-from-zero, while some languages and statistical systems default to banker’s rounding. For binary floating-point, the stored value is already an approximation, so ROUND may not produce the decimal result a user expects. For reproducible significant-figure behavior, cast to DECIMAL with sufficient scale before rounding, or perform the rounding in a controlled application layer. See Banker’s Rounding and SQL and databases.

Discipline Note

In engineering and metrology, significant figures are tied to measurement uncertainty and specification conformance (ASTM E29, GUM). In chemistry and physics, significant-figure rules are used to avoid implying false precision. In database design, the same principle becomes an architecture decision: store the raw value, store the uncertainty or calibration metadata, and round only when producing a report, certificate, or user interface. This site is a precision-and-rounding reference, not just a calculator; the goal is to help you choose the right convention before the data reaches SQL.

Quick Reference Table

Sources & Further Reading

• ASTM E29-13, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications.
• ISO 80000-1:2009, Quantities and units — Part 1: General, clause 7.3.
• JCGM 100:2008, Evaluation of measurement data — Guide to the expression of uncertainty in measurement (GUM), clause 7.2.
• NIST Technical Note 1297, Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results, Section 4.6.
• ISO/IEC 9075-2:2016, Information technology — Database languages — SQL — Part 2: Foundation, for exact and approximate numeric types.

Related internal references: Significant Figures, Rounding Rules, Rounding Methods, Precision, Measurement Uncertainty.