A total that should be 100 displays 99.99999999, and an equality filter misses 0.3. FLOAT math stores approximations to many decimal fractions, so the arithmetic can surprise anyone expecting exact cents. The fix starts with choosing the right data type for the meaning of the number.
See FLOAT Math Drift in a Tiny Query
FLOAT is an approximate numeric type based on binary floating-point representation. Decimal values such as 0.1 have no short exact binary form. SQL Server stores a nearby value. Add two approximations and compare them with another approximation, and the last bits can differ. Use a high-precision display to see the effect rather than relying on a client that rounds the result for display.
DECLARE @a float = 0.1;
DECLARE @b float = 0.2;
DECLARE @c float = 0.3;
SELECT @a + @b AS float_sum,
@c AS float_target,
CASE WHEN @a + @b = @c THEN 1 ELSE 0 END
AS exact_equality,
@a + @b - @c AS difference;The displayed decimal tail depends on formatting and the particular expression, but the point is stable: FLOAT is approximate. I use this example when a report owner asks why two visually identical values do not join. The answer is in representation, not in the SUM function. What does the number represent in your application, and how exact must it be?
Repeat With DECIMAL
DECIMAL stores fixed-precision decimal values. When the required range and scale are chosen correctly, cents and other fixed decimal quantities can be represented as intended. Declare the precision and scale explicitly. DECIMAL(19,4) is one common example for money-like values, but the right choice depends on maximum amount and smallest unit.
DECLARE @a decimal(19,4) = 0.1;
DECLARE @b decimal(19,4) = 0.2;
DECLARE @c decimal(19,4) = 0.3;
SELECT @a + @b AS decimal_sum,
@c AS decimal_target,
CASE WHEN @a + @b = @c THEN 1 ELSE 0 END
AS exact_equality;DECIMAL still requires care. Arithmetic can increase scale or overflow the declared precision, and rounding rules matter. Do not promise that changing a column to DECIMAL fixes every calculation. Review expressions, casts, divisions, and application types along the full path from input to report.
Watch FLOAT Math Accumulate Error in a SUM
A SUM over FLOAT values adds approximate inputs. The final result can land just above or below the expected mathematical total. The order of addition can also affect the low bits, so parallel and serial plans can produce slightly different final tails. This is normal for floating-point arithmetic and a poor basis for exact equality tests.
WITH values_to_add AS
(
SELECT TOP (1000) CAST(0.1 AS float) AS amount
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b
)
SELECT SUM(amount) AS float_total,
SUM(CAST(amount AS decimal(19,4))) AS decimal_total
FROM values_to_add;This query intends 100.0 from one thousand copies of 0.1. Read both results with enough precision to see any difference. Do not use the converted DECIMAL total as proof that existing FLOAT data was originally exact; the conversion rounds each stored approximation. For historical financial data, compare against authoritative source records before declaring a repaired total.
Use a Tolerance When FLOAT Math Is Appropriate
FLOAT is suitable for measurements, scientific values, and calculations where approximate representation is acceptable. Compare values within an application-defined tolerance rather than with exact equality. The tolerance should reflect the scale and acceptable error, not a magic number copied from a blog. For values across many orders of magnitude, a relative tolerance can be more useful than one fixed absolute amount.
DECLARE @left float = 0.1;
DECLARE @right float = 0.2;
DECLARE @measured float = @left + @right;
DECLARE @target float = 0.3;
DECLARE @tolerance float = 0.000000001;
SELECT CASE
WHEN ABS(@measured - @target) <= @tolerance
THEN 1 ELSE 0
END AS close_enough;The example adds two FLOAT variables. Writing 0.1 + 0.2 without casts would perform decimal arithmetic in T-SQL before assignment and could change the result. Implicit conversions and literal types therefore matter. In a real comparison, use the FLOAT values stored by the application and document the tolerance rationale.
Choose Types by Meaning
Prices, account balances, tax amounts, and counts need exact types. Use DECIMAL for fixed-scale amounts and integer types for counts. Physical measurements and statistical models can use FLOAT when their error budget allows it. Approximate does not mean broken; it means the representation has a different contract. Make that contract visible in the schema and API.
I also inspect client code. A decimal SQL column converted to a binary floating-point type in the application loses the benefit before the calculation begins. Drivers, ORMs, serialization, and spreadsheet exports all belong in the review. A database type change alone is not enough if the value comes back as a double and is compared exactly.
Convert an Existing Column in Stages
Do not ALTER a large FLOAT column in place without a data and application plan. Inventory the range, nulls, values near scale boundaries, and rows that would round or overflow under the target DECIMAL. Add a new column in a test copy and backfill it in batches. Compare old and new values with an approved tolerance, then update the application to read and write the new column. Keep a rollback route until reports reconcile.
SELECT COUNT_BIG(*) AS rows_that_do_not_fit
FROM dbo.Measurements
WHERE FloatAmount IS NOT NULL
AND TRY_CONVERT(decimal(19,4), FloatAmount) IS NULL;Replace the table and column. This finds conversion failures, not rounding differences. Run a second comparison for values whose rounded decimal differs beyond the allowed tolerance. Indexes, computed columns, and downstream consumers can depend on the old type. Review them before a final rename or removal. I would rather migrate carefully than fix one report while breaking ten integrations.
Read the Boundary Cases Before Migrating
The largest amount is not the only migration test. Check negative values, zeros, very small fractions, and values close to the chosen maximum. A scale of four turns a measurement with six meaningful decimal places into a rounded value. That change can be acceptable for one report and destructive for another. Write down the unit and required resolution with the data owner. Then compare aggregates and individual outliers after conversion. An average that matches can conceal individual values that moved in opposite directions. Include a sample of individual rows in the sign-off, and keep the original values until the new representation has passed reconciliation with the source system.
Exact decimal arithmetic also has rules for division. Dividing two integers can truncate before an assignment to DECIMAL, and dividing decimal values can produce a different scale than expected. Cast operands before the operation when necessary, then round intentionally at the business boundary. Test the expression that the application actually runs, not only the storage column.
Related reading on this blog: Datatype Decimal Explained: Datatype Numeric and Banker's Rounding.
FLOAT is not exact accounting math, it is an approximate type for measured values.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
