Float value changes when rounded

by

Summary

This incident centers on a classic floating‑point precision problem in SQL Server: a value stored as a float appears as 10000 when selected normally, but becomes 9999 when cast to int. The discrepancy is caused by hidden binary rounding errors inherent to floating‑point storage.

Root Cause

The root cause is the inexact binary representation of decimal numbers when using SQL Server’s float type.

Key points:

  • float stores values in IEEE‑754 binary floating‑point, not decimal.
  • Many decimal values cannot be represented exactly in binary.
  • SQL Server rounds the displayed value for readability, hiding the true stored value.
  • When casting to int, SQL Server truncates the underlying binary value, revealing the hidden precision error.

Why This Happens in Real Systems

Floating‑point issues appear in production systems because:

  • Percentages, ratios, and financial calculations often produce values that cannot be represented exactly.
  • Developers assume float behaves like a decimal type.
  • SQL Server’s default formatting hides trailing binary noise, giving a false sense of accuracy.
  • Casting or comparing values forces SQL Server to use the actual stored value, not the rounded display value.

Real-World Impact

These issues can cause subtle but serious production failures:

  • Incorrect financial rounding (e.g., 10000 becomes 9999)
  • Mismatched joins when comparing floats
  • Unexpected WHERE clause behavior
  • Silent data corruption in reporting pipelines
  • Off‑by‑one errors in business logic

Example or Code (if necessary and relevant)

DECLARE @x FLOAT = 10000 * 0.9999;
SELECT @x AS raw_value, CAST(@x AS INT) AS cast_to_int;

How Senior Engineers Fix It

Experienced engineers avoid float for business‑critical numeric data.

Typical fixes:

  • Use DECIMAL(p, s) for financial or exact numeric values.
  • Normalize calculations to avoid unnecessary floating‑point operations.
  • Apply ROUND() explicitly when converting or comparing values.
  • Audit schemas to eliminate inappropriate use of floating‑point types.
  • Add unit tests that catch precision‑related regressions.

Why Juniors Miss It

Common reasons junior engineers overlook this issue:

  • They assume float behaves like a decimal type.
  • They trust the displayed value, not realizing SQL Server rounds it.
  • They are unfamiliar with IEEE‑754 binary precision rules.
  • They rarely test edge cases like casting, comparisons, or large values.
  • They underestimate how often floating‑point errors appear in real systems.

Key takeaway: float is not a safe type for money, percentages, or anything requiring exactness.

Leave a Comment