Decimal Multiplication: Precision Can Reduce the Result Scale

Decimal Multiplication derives a result type from its operand types, and the precision limit can reduce the scale. I inspect three small products. A small numeric value does not necessarily imply a narrowly declared arithmetic result.

Precision describes total digits, while scale describes fractional digits. The declared operand types influence both. A wider declaration can therefore change the available fractional scale even when the actual values remain modest.

Compare three declared operand types

The first query multiplies decimal(10,2) operands. The second uses decimal(20,8), and the third uses decimal(30,10). Each contains two fixed rows so the output includes more than one value.

A Products expression performs multiplication before any display conversion. SQL_VARIANT_PROPERTY inspects the product’s precision and scale. The query returns those diagnostics beside the actual product.

The values are deliberately small enough to avoid overflow. No session options, objects or error-producing statement are required. Each result set has an explicit case order.

WITH Inputs AS
(
    SELECT CaseId, CAST(A AS decimal(10,2)) AS A, CAST(B AS decimal(10,2)) AS B
    FROM (VALUES (1, 1.25, 2.00), (2, 2.50, 3.00)) AS v(CaseId, A, B)
), Products AS
(
    SELECT CaseId, A * B AS Product FROM Inputs
)
SELECT CaseId, Product,
       CAST(SQL_VARIANT_PROPERTY(Product, 'Precision') AS int) AS ResultPrecision,
       CAST(SQL_VARIANT_PROPERTY(Product, 'Scale') AS int) AS ResultScale
FROM Products ORDER BY CaseId;

WITH Inputs AS
(
    SELECT CaseId, CAST(A AS decimal(20,8)) AS A, CAST(B AS decimal(20,8)) AS B
    FROM (VALUES (1, 1.23456789, 2.00000000), (2, 0.00000001, 1.00000000)) AS v(CaseId, A, B)
), Products AS
(
    SELECT CaseId, A * B AS Product FROM Inputs
)
SELECT CaseId, Product,
       CAST(SQL_VARIANT_PROPERTY(Product, 'Precision') AS int) AS ResultPrecision,
       CAST(SQL_VARIANT_PROPERTY(Product, 'Scale') AS int) AS ResultScale
FROM Products ORDER BY CaseId;

WITH Inputs AS
(
    SELECT CaseId, CAST(A AS decimal(30,10)) AS A, CAST(B AS decimal(30,10)) AS B
    FROM (VALUES (1, 0.1234567890, 1.0000000000), (2, 0.0000009000, 1.0000000000)) AS v(CaseId, A, B)
), Products AS
(
    SELECT CaseId, A * B AS Product FROM Inputs
)
SELECT CaseId, Product,
       CAST(SQL_VARIANT_PROPERTY(Product, 'Precision') AS int) AS ResultPrecision,
       CAST(SQL_VARIANT_PROPERTY(Product, 'Scale') AS int) AS ResultScale
FROM Products ORDER BY CaseId;

Read the type and value together

The first products should use precision 21 and scale four. Their expected values are 2.5000 and 7.5000. The result accommodates the multiplication of both declared operand ranges.

The second products should use precision 38 and scale 13. Their values remain 2.4691357800000 and 0.0000000100000. The original theoretical scale is reduced, but these particular values still fit exactly.

The third products should use precision 38 and scale six. The expected values are 0.123457 and 0.000001. Those outputs demonstrate rounding of fractional detail during the intermediate multiplication.

The trailing zeros in the expected values reflect the declared scale. A query tool can choose a different visual presentation. The precision and scale columns are the direct metadata witnesses.

Account for the intermediate expression

The basic multiplication rule adds both operand precisions and one extra digit. It adds both operand scales. When the proposed precision exceeds 38, SQL Server reduces the scale to make the result fit.

For the second query, the theoretical precision is 41 and scale 16. Its integral allowance is 25 digits. That leaves 13 fractional digits under the maximum precision.

For the third query, the theoretical integral allowance is much larger, while the theoretical scale exceeds six. The applicable rule sets scale to six. The small actual values do not bypass the declared-type calculation.

I don’t infer the rule from a formatted screenshot alone. The result type must be inspected with the value. A visually short number can hide a much wider expression contract.

Choose types for the calculation

A final cast happens after the intermediate product is computed. Casting that product to a larger scale cannot restore digits already rounded away. Choose suitable operand types before the arithmetic if those digits matter.

Choosing narrower operands is not automatically safe for every possible input. Their declared ranges must still hold the real data. Validate the input domain as well as the desired output scale.

The example establishes no money calculation policy or universal rounding preference. It isolates an arithmetic type rule. Keep both representative values and boundary values when designing a production decimal expression.

Read the type next to the value, and the surprise goes away.

A small value is not a small type, it is a number inside a wide declaration.

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