Decimal Precision and Rounding in SQL Server

The invoice total is off by a cent, and every individual line looks reasonable. Decimal precision and rounding rules can explain the difference before you blame the report.

Separate Decimal Precision From Scale

For decimal(p,s), precision is the total number of digits and scale is the number after the decimal point. decimal(10,2) permits two fractional digits and eight digits before the point. The choice defines valid values, storage, and arithmetic behavior. It should come from the business range and smallest meaningful unit.

I ask whether a value is currency, a rate, a quantity, or a scientific measure. They need different scales. An exchange rate can need more fractional digits than an invoice total. Storing both in decimal(18,2) because they are “numbers” loses useful information.

Check the largest expected value as well as the smallest increment. A type that handles cents but overflows on a future aggregate is incomplete. The type belongs to the calculation chain, not only the input column.

SELECT SQL_VARIANT_PROPERTY(CONVERT(decimal(10,2), 123.45), 'Precision') AS PrecisionValue,
       SQL_VARIANT_PROPERTY(CONVERT(decimal(10,2), 123.45), 'Scale') AS ScaleValue;

Watch Decimal Precision in the Result Type of Arithmetic

SQL Server derives precision and scale for addition, multiplication, and division from the operand types. The resulting scale can be reduced when the maximum precision would be exceeded. A calculation can therefore round or overflow where a developer expected unlimited decimal places.

Cast inputs deliberately at important boundaries. Do not wrap every expression in decimal(38,20) without considering the integer range and the eventual target type. Test the full expression, including multiplication by rates and final conversion into a stored column.

I inspect SQL_VARIANT_PROPERTY for a small expression when the inferred type surprises me. It shows what SQL Server chose. Then I check the value at edge cases, because knowing the type is only part of understanding the result.

SELECT SQL_VARIANT_PROPERTY(
           CONVERT(decimal(12,2), 100.25) * CONVERT(decimal(9,4), 0.0750),
           'Scale'
       ) AS ResultScale;

Decide Where Rounding Belongs

ROUND changes a value at a specified decimal position, but the business rule decides when to apply it. Rounding each line item before summing can differ from summing unrounded lines and rounding the total. Both calculations can be valid under different billing contracts. Make the chosen stage explicit.

I keep raw calculated values long enough to reconcile them with the final posted amount. If the report rounds only for display, it should not use displayed values as a substitute for stored accounting amounts. Presentation and ledger rules are different things.

What does your invoice policy say about a fractional cent? The answer should be written before SQL is changed. A one cent difference is small in a single row and large in a reconciliation meeting.

WITH x AS
(
    SELECT CONVERT(decimal(9,4), 0.3350) AS LineAmount
    UNION ALL SELECT CONVERT(decimal(9,4), 0.3350)
)
SELECT SUM(ROUND(LineAmount, 2)) AS RoundThenSum,
       ROUND(SUM(LineAmount), 2) AS SumThenRound
FROM x;

Understand Conversion, Decimal Precision, and Silent Rounding

Converting a decimal with more fractional digits to a smaller scale rounds the value under SQL Server’s conversion rules. It does not preserve the extra digits. An INSERT into a decimal(10,2) column can therefore change a decimal(10,4) source value without a syntax error.

That behavior is useful when it matches the contract and dangerous when the source expected exact preservation. Add a validation query for values that would change, or store the higher precision input separately. Do not call every changed value “bad” until the business rule is clear.

I test positive and negative values near the rounding boundary. Negative amounts, credits, and rates can expose assumptions from a sample that contained only positive invoices. Put those cases in the calculation test set.

SELECT CONVERT(decimal(10,2), CONVERT(decimal(10,4), 12.3450)) AS RoundedValue;

Choose Between money and decimal

money has a fixed four-place scale and a specific range. It can be convenient for stored amounts, but intermediate arithmetic can still produce surprising rounding. decimal lets you choose precision and scale for the domain. Neither type automatically guarantees correct financial rules.

I prefer that type when a calculation needs a documented precision choice across rates, quantities, and totals. If an existing system uses money, test its actual expressions rather than replacing the type by instinct. A migration affects clients, indexes, and reports as well as storage.

Avoid float for exact currency amounts. Binary floating point represents many decimal fractions approximately. A display format can hide the approximation until comparison or aggregation reveals it. Use exact numeric types when the domain requires exact decimal behavior.

SELECT CONVERT(decimal(19,4), CONVERT(money, 12.3456)) AS MoneyAsDecimal,
       CONVERT(decimal(19,4), 12.3456) AS NativeDecimal;

Check Aggregates and Overflow

SUM of many rows can exceed the range planned for a single row. Confirm the result type and the destination type for stored summaries. A dashboard total can fail even when every source amount fits its column. Include expected growth in the design.

A grouped total also depends on join grain. Duplicate rows from a join can look like a rounding issue because the final number is wrong. Reconcile row counts and keys before adjusting decimal types. Arithmetic cannot repair a multiplied input set.

I compare a direct base-table total with any summary table under the same filter. Then I inspect rounding stage and result type. That order prevents a long debate over cents when the true problem is duplicated orders.

Build a Small Calculation Test

Create test cases for maximum range, fractional boundary, negative values, zero, NULL, and a multi-line sum. Write expected outputs from the business rule, then run the SQL expression. A passing type check alone does not prove the policy was implemented.

Use explicit casts in examples and procedures where type inference is important. Document the final rounding point in the calculation, not only in a report note. If the rule changes, update the test expectations and review downstream summaries.

Decimal precision is a design choice with visible business results. Choose the range and scale, test SQL Server’s derived type, and make rounding a named rule. That gives the next cent a reason to land where it does.

Round at the boundary where a business rule requires rounding, not after every intermediate calculation. Early rounding can accumulate differences across line items, while storing more scale than necessary can complicate comparisons. Which result does the business regard as authoritative: the sum of rounded lines or the rounded sum? Put that rule in a test with positive and negative values, then use the same rule in reports and exports.

Related reading on this blog: Banker's Rounding and Datatype Decimal Explained: Datatype Numeric.

A decimal type is not a guarantee of correct money math, it is a container for a defined rounding rule.

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