Significant Figures in SQL and Databases: Storing and Querying Numeric Precision

Rule Statement

Significant figures are a property of a measured or reported quantity, not a property that SQL stores as metadata. A database column such as DECIMAL(10,2) records precision and scale; it does not record how many digits are significant, how uncertain the value is, or whether a trailing zero was intended to be meaningful. The governing rule is therefore: model significant figures explicitly, store the value in an appropriate numeric type, and apply significant-figure rounding only at final reporting.

1. Determine significance before storage. Apply the measurement rules from your discipline, not the column definition. See Significant Figures and Ambiguous Trailing Zeros.
2. Choose exact numeric types for exact decimal quantities. Use DECIMAL or NUMERIC when decimal values and declared scale matter. Use INTEGER or BIGINT for counts and identifiers.
3. Use approximate types only when binary floating-point is acceptable. REAL, FLOAT, and DOUBLE PRECISION follow IEEE 754-style behavior in most engines and cannot reliably preserve decimal trailing zeros.
4. Store uncertainty separately. The Guide to the Expression of Uncertainty in Measurement (GUM) treats value and uncertainty as distinct quantities. A column for value and another for standard_uncertainty is usually better than encoding precision in the value alone.
5. Round once, at the end. Intermediate rounding in CTEs, views, or application layers causes double-rounding bias and inflated apparent precision.

SQL precision is not the same as measurement precision. DECIMAL(10,2) does not mean two significant figures; it means two digits after the decimal point.

Worked Examples

Example 1: A trailing zero in DECIMAL

A measured length is reported as 12.30 cm. Under standard significant-figure rules, the trailing zero after the decimal point is significant, so the value has four significant figures. A suitable SQL column is DECIMAL(6,2):

CREATE TABLE measurements (id INT, length_cm DECIMAL(6,2));

Inserting 12.30 stores the exact decimal value in most engines. However, if you compute SELECT AVG(length_cm) FROM measurements;, the result may display many decimal places. Do not report them all. Round according to the uncertainty of the mean, not merely the column scale.

Example 2: Multiplication and division

Suppose a rectangle is measured as 2.5 cm by 3.42 cm. The product is 2.5 × 3.42 = 8.55 cm². Since 2.5 has two significant figures and 3.42 has three, the result should be rounded to two significant figures: 8.6 cm². In SQL, the calculation can be exact—SELECT 2.5 * 3.42;—but the rounding should be applied at the reporting stage: ROUND(2.5 * 3.42, 1).

Example 3: Value and uncertainty together

Store a result as value NUMERIC(12,6) and u NUMERIC(12,6). If the computed standard uncertainty is 0.0001234, GUM Clause 7.2.6 recommends reporting it to at most two significant digits, e.g. 0.00012. The value should then be rounded to the same decimal place, not to an arbitrary number of decimals.

Counter-Examples

• Mistaking scale for significant figures. DECIMAL(10,2) can store 12345678.90; the scale is 2, but the significant figures are 10.
• Testing exact equality with FLOAT. In binary floating-point, 0.1 + 0.2 = 0.3 may evaluate to false. Use exact numeric types or a tolerance derived from uncertainty.
• Double rounding. Rounding 1.245 to two decimals gives 1.25; rounding that to one decimal gives 1.3. Direct rounding to one decimal gives 1.2. The first path is wrong because it rounds twice.
• Losing significant trailing zeros. Storing 0.001230 in FLOAT may display as 0.00123, silently removing the fourth significant figure.
• Rounding every CTE. Repeated ROUND calls in a query pipeline accumulate error and can change the final significant-figure decision.

Convention Comparison Table

Standards Citation

• ISO 80000-1:2022, Quantities and units — Part 1: General, Clause 7.3: rounding and the number of digits retained in reported numerical values.
• ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, Section 6: rounding rules and significant-digit practice for test data.
• JCGM 100:2008 (GUM), Clause 7.2.6: uncertainty should be reported to at most two significant digits, and rounding should occur only at final reporting.
• NIST TN 1297, Section 5: expression of uncertainty and significant digits in U.S. metrology practice.
• ISO/IEC 9075-2:2023, SQL Foundation, Subclause 4.5: exact numeric types and approximate numeric types.

For more on these sources, see our GUM Guide, ASTM E29, and ISO 80000 reference pages.

Common Mistakes

• Assuming the database enforces significant figures. SQL engines enforce type compatibility and scale, not measurement rules.
• Using FLOAT for decimal measurements. Binary floating-point cannot exactly represent most decimal fractions, so trailing zeros and decimal equality become unreliable.
• Rounding before aggregation. Sums, averages, and standard deviations should be computed from unrounded values, then rounded once.
• Encoding significance only in display format. If a trailing zero is significant, a display format is not enough; store the scale or a separate significant-figures field.
• Ignoring uncertainty in query output. A query that returns 12.345678 may imply precision the measurement never had.

Software Behavior Note

SQL engines differ in how they store and display exact decimals, especially trailing zeros. The following is a practical summary; always verify against your exact DBMS version.

Because behavior differs, a robust scientific schema often stores value, uncertainty, and significant_figures as separate columns.

Quick Reference Table

Sources & Further Reading

• ISO 80000-1:2022, Quantities and units — Part 1: General.
• ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications.
• JCGM 100:2008, Evaluation of measurement data — Guide to the expression of uncertainty in measurement (GUM).
• NIST TN 1297, Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results.
• ISO/IEC 9075-2:2023, Information technology — Database languages SQL — Part 2: Foundation.

For broader rules, see Significant Figures, Multiplication and Division, Precision, and Measurement Uncertainty.