Decimal Arithmetic in SQL Server: Precision Before Rounding

A column declared decimal does not make every calculation leading to that column exact. SQL Server evaluates intermediate expressions using the types of their operands. Information can be lost before the final assignment, and an aggregate can overflow even when the destination column is large enough. These errors are particularly awkward in billing because the numbers often look reasonable.

Start with the business quantity. Money, exchange rates, percentages, measurements, and counts need different ranges and scales. decimal(p,s) allows p total digits, including s digits after the decimal point. decimal(12,2), for example, leaves ten digits before the decimal point. Choosing scale without calculating the largest expected magnitude is only half a design.

Control the intermediate expression

The following expressions demonstrate why casting the final result can be too late.

SELECT 5 / 2 AS IntegerResult,
       CAST(5 AS decimal(12,4)) / 2 AS DecimalResult,
       CAST(5 / 2 AS decimal(12,4)) AS CastTooLate;

Integer division produces 2. Casting an operand first retains the fractional result, 2.5. Casting the already computed integer result produces 2.0000; it cannot recover the discarded half.

The same principle applies to multiplication. A large int multiplied by another int can overflow before a surrounding conversion to bigint. Convert an operand before the multiplication. Do not assume a destination column changes how the source expression is evaluated.

Decimal expressions also have derived precision and scale. For multiplication, the initial rule adds operand scales and uses p1 + p2 + 1 precision. SQL Server caps decimal precision at 38 and applies further rules that can reduce scale or still leave an overflow. Division can expand scale substantially. A chain of casts to decimal(38,...) is therefore not a universal way to preserve every digit.

Work backward from the acceptable final rounding error, maximum quantity, unit price, and exchange rate. Select intermediate types that can represent the largest product, then make the final conversion explicit. Test boundaries as well as ordinary values. Where client libraries expose parameter precision and scale, set them deliberately rather than letting different values produce different inferred parameter definitions.

Decide where rounding belongs

Two mathematically defensible invoice policies can produce different totals.

DECLARE @Lines table (Amount decimal(10,3));
INSERT @Lines VALUES (19.995), (19.995);
SELECT SUM(ROUND(Amount, 2)) AS RoundedPerLine,
       ROUND(SUM(Amount), 2) AS RoundedInvoice
FROM @Lines;

Rounding each 19.995 line to two decimal places and then summing yields 40.000. Summing first and rounding the invoice yields 39.990. The extra displayed zero reflects the expression's retained scale; the meaningful difference is one cent.

Neither policy should be selected accidentally by the placement of ROUND. Agree whether tax and discounts are calculated per line or per invoice, in what order, and where any remainder is allocated. SQL Server ROUND resolves a halfway tie away from zero. If another system uses a different tie-breaking rule, identical input values can produce different settled amounts.

Do not silently replace decimal with float to avoid overflow. float is an approximate binary representation and is unsuitable when exact decimal agreement is part of the contract. Conversely, scientific measurements may legitimately use approximate types. Choose from the requirement, not from a blanket rule that one type is always superior.

Aggregate in the right type

SUM over an int expression returns int, even if the answer is later assigned to bigint. Widen the input expression before aggregation.

DECLARE @Counts table (Quantity int);
INSERT @Counts VALUES (2000000000), (2000000000);
SELECT SUM(CONVERT(bigint, Quantity)) AS TotalQuantity
FROM @Counts;

The result is 4,000,000,000. Converting SUM(Quantity) afterward would leave the aggregate's overflow unresolved. SUM of decimal inputs returns decimal(38,s), but this still has a finite number of integer digits and cannot accommodate arbitrary growth.

Specify how missing values should behave. SUM ignores NULL inputs and returns NULL when there are no non-NULL inputs. Replacing that result with zero may be correct for a count-like report but may conceal missing financial data. A zero price and an unknown price are different facts.

A practical regression set includes fractional division, maximum magnitudes, negative refunds, halfway rounding cases, empty inputs, and reconciliation against the application's calculation. Check the values at each intermediate step rather than only the formatted final string. Formatting can hide precision loss; it cannot repair it.

Technical references: Microsoft Learn: Precision, scale, and length · Microsoft Learn: SUM.