float vs decimal: Why Money Should Never Be float

A total that looks right on the screen can still be wrong in a comparison. The float vs decimal choice determines whether currency uses approximate or exact arithmetic. Formatting the answer doesn't change the stored value.

Float vs Decimal Means Approximate vs Exact

Float and real store approximate numeric values using binary representation. Decimal stores an exact value within a declared precision and scale. Neither type has infinite capacity.

They make different promises. Approximation works well for scientific measurements. Currency calculations normally need an exact decimal contract, including how many fractional digits you retain before the final rounding step.

I check the source column types before investigating a mysterious difference in totals. An exact destination column doesn't repair arithmetic already performed as float. SQL Server's type precedence can bring an approximate value into a mixed expression.

Trace the whole calculation, including procedure parameters and application bindings. The float vs decimal decision follows the money through the system, not only into the final table.

Inspect a Familiar Fraction Carefully

The familiar expression 0.1 plus 0.2 illustrates binary approximation, but SQL Server adds an important detail. Bare literals such as 0.1 are exact numeric values, not float. Cast them explicitly to test approximate arithmetic.

Display tools can round the same value differently. Trust the equality flag over the grid, and inspect both calculations on your server.

The test below returns the approximate sum, a higher-scale conversion, and an equality check. It also returns the exact decimal version. A rounded display can show the same characters for different representations.

Equality operates on the typed values. That is the reason a result grid isn't a reliable proof that an approximate type is safe for cents.

On my server the approximate sum displayed as 0.30000000000000004, and the equality flag came back 0. The exact version returned .3000.

DECLARE @A float = 0.1, @B float = 0.2, @C float = 0.3;
SELECT @A + @B AS ApproximateSum,
       CONVERT(decimal(38,20),@A + @B) AS ExpandedSum,
       CASE WHEN @A + @B = @C THEN 1 ELSE 0 END AS EqualFlag;
SELECT CONVERT(decimal(19,4),0.1) + CONVERT(decimal(19,4),0.2) AS ExactSum;

Compare Float, Real, Decimal, and Money Together

Real provides less approximate precision than the usual float declaration. Float without a smaller precision declaration uses its wider form. Money is a fixed-scale type with four fractional digits.

Decimal lets you choose the required precision and scale. These choices affect intermediate arithmetic as well as storage. A currency symbol is a display concern, not a reason to choose money automatically.

Use identical source values when comparing the types. The table variable below stores the same sample amounts in four columns. Inspect each sum without treating those results as production evidence.

For representative testing, include small fractions, large amounts, and negative adjustments. A design that handles only pleasant positive values hasn't finished its review. Credits belong to the arithmetic too.

My run returned 0.60000000000000009 for the float column and 0.60000001639127731 for the real column. The exact and money columns both returned .6000.

DECLARE @Amounts TABLE
(
    ApproximateValue float,
    SmallApproximateValue real,
    ExactValue decimal(19,4),
    MoneyValue money
);
INSERT @Amounts VALUES (0.1,0.1,0.1,0.1),(0.2,0.2,0.2,0.2),(0.3,0.3,0.3,0.3);
SELECT SUM(ApproximateValue) AS FloatTotal,
       SUM(SmallApproximateValue) AS RealTotal,
       SUM(ExactValue) AS DecimalTotal,
       SUM(MoneyValue) AS MoneyTotal
FROM @Amounts;

Keep Division at the Required Scale

Money's four-place scale can lose fractional detail during intermediate operations. Decimal division follows precision and scale rules, with a maximum precision of 38. That still needs deliberate casts when the required intermediate scale differs.

Decide where rounding occurs in a financial calculation. Rounding every line and rounding the invoice total can produce different answers under valid business rules.

I make that rule explicit before changing a type. The following comparison performs division before multiplication. It shows why a fixed-scale intermediate matters.

Use the returned values to inspect your own engine's behavior. Don't call one expression correct until it matches the approved rounding policy. Arithmetic cannot choose the accounting rule, even when the query has a convincing column alias.

Here the money expression returned .9999, while the decimal(19,8) expression returned 1.0000000. The ROUND example turned 10.005 into 10.0100.

SELECT CAST(1 AS money) / CAST(3 AS money) * CAST(3 AS money) AS MoneyCalculation,
       CAST(1 AS decimal(19,8)) / CAST(3 AS decimal(19,8))
       * CAST(3 AS decimal(19,8)) AS DecimalCalculation;
SELECT ROUND(CAST(10.005 AS decimal(19,4)),2) AS RoundedAmount;

Expect Aggregation to Expose the Float vs Decimal Gap

Adding approximate values accumulates rounding effects. The order of addition matters because floating-point arithmetic isn't associative. Parallel execution and changed access paths can expose differences in the low-order digits.

The engine still follows approximate numeric semantics. Don't diagnose every tiny total difference as corruption. First establish whether the schema promised an exact sum in the first place.

For a controlled comparison, create repeated sample fractions and aggregate both representations. The sample row generation isn't an observed workload size. Keep the exact sum beside the approximate one.

Inspect differences after a deliberate conversion to a sufficiently wide decimal type. Be careful with equality filters on approximate columns. A rounded label isn't a stable join key or currency predicate.

Ten thousand additions of 0.1 as float returned 1000.0000000001588 on my server. The exact column returned 1000.0000.

WITH Numbers AS
(
    SELECT TOP (10000) 1 AS SampleValue
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
SELECT SUM(CAST(0.1 AS float)) AS ApproximateTotal,
       SUM(CAST(0.1 AS decimal(19,4))) AS ExactTotal
FROM Numbers;

Choose Precision for the Whole Domain

Decimal(19,4) is a common currency choice, but it isn't a universal financial specification. Precision includes every digit, while scale counts fractional digits. Allow room for maximum balances and intermediate calculations.

Rates and exchange factors need their own scales. Casting everything to two decimals at ingestion throws away detail you cannot recover during a later audit.

Which amount and rounding rule must your application preserve? Write that down before altering columns. Profile current values and test conversions in a separate copy.

Converting existing float data to decimal makes the chosen rounded representation exact afterward. It doesn't reveal the original intended amount. Preserve the original values during reconciliation instead of assuming a type change fixes their history.

Carry the Float vs Decimal Rule Across Boundaries

Review client parameters, staging tables, computed expressions, and exports together. An application sending float values can reintroduce approximation before SQL Server receives them. Decimal output can also be converted back by another consumer.

Match the numeric contract across that boundary. The database schema is only one stop in the calculation's life, and the whole route needs the same promise.

Use float vs decimal as a correctness decision before treating it as a storage decision. Keep exact fixed-scale decimals for currency and explicit rules for rounding. Validate sums, division, and comparisons with representative values.

A calculator-looking grid is reassuring, but it doesn't certify the type. The cents need a stronger contract than a pleasing number of displayed digits.

For equality after importing approximate values, don't invent a universal tolerance. A tolerance suitable for a physical measurement doesn't define a currency balance. Reconcile against the original approved amounts where available.

Keep conversion exceptions visible. If a value overflows the selected decimal precision, flag it for review. Don't silently reduce the scale.

Related reading on this blog: Why FLOAT Math Does Not Add Up in T-SQL Queries and Datatype Decimal Explained: Datatype Numeric.

Currency arithmetic is not a formatting choice, it is an exact-value contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.