Why is there precision loss when multiplying DECIMAL in SQL Server?

Question

Run this script in SQL Server (My version is SQL Server 2016):

DECLARE @num1 DECIMAL(20,7)
DECLARE @num2 DECIMAL(20,7)
SET @num1 = 0.0000005
SET @num2 = 1.0
SELECT @num1 * @num2 AS Result

Result:

Result
---------------------------------------
0.00000050000

The result is 0.00000050000 in scale DECIMAL(X,11)

Now, if we change the script as follow:

DECLARE @num1 DECIMAL(21,7)
DECLARE @num2 DECIMAL(20,7)
SET @num1 = 0.0000005
SET @num2 = 1.0
SELECT @num1 * @num2 AS Result

Result:

Result
---------------------------------------
0.0000005000

You can see the result is 0.0000005000, it seems like the scale was reduced as DECIMAL(X,10)

Continue to increase the precision of @num1:

DECLARE @num1 DECIMAL(22,7)
DECLARE @num2 DECIMAL(20,7)
SET @num1 = 0.0000005
SET @num2 = 1.0
SELECT @num1 * @num2 AS Result

Result:

Result
---------------------------------------
0.000000500

The result is 0.000000500 now, it seems like the scale was reduced as DECIMAL(X,9)

I want to ask what the exact precision and scale after two DECIMAL numeric calculated is. It seems like there will be serious precision loss, if we multiplied two DECIMAL numeric. The following script will give totally wrong result due to precision loss, which will round fraction that exceeds scale of the result.

DECLARE @num1 DECIMAL(25,7)
DECLARE @num2 DECIMAL(20,7)
DECLARE @num3 DECIMAL(25,7)
DECLARE @num4 DECIMAL(20,7)
SET @num1 = 0.0000005
SET @num2 = 1.0
SET @num3 = 0.0000004
SET @num4 = 1.0
SELECT @num1 * @num2 AS Result1, @num3 * @num4 AS Result2

Result:

Result1                                 Result2
--------------------------------------- ---------------------------------------
0.000001                                0.000000

Answer 1

The actual precision and scale, when you make the multiplications in the question, are calculated according to these rules:

• The result precision and scale have an absolute maximum of 38.
• Result precision is p1 + p2 + 1, result scale is s1 + s2 (p1 and s1 are precision and scale for the first expression, p2 and s2 are precision and scale for the second expression).
• The resulting scale is reduced to min(scale, 38 - (precision-scale)) if the integral part is less than 32, because it can't be greater than 38 - (precision-scale). Result might be rounded in this case

So, in your example:

DECLARE @num1 DECIMAL(20,7)
DECLARE @num2 DECIMAL(20,7)
SET @num1 = 0.0000005
SET @num2 = 1.0
SELECT @num1 * @num2 AS Result

the actual calculation for scale is:

• 7 + 7 = 14
• min (14, 38 - ((20 + 20 + 1) - (7 + 7))) = 11

Answer 2

This is all to do with Precision, scale, and Length (Transact-SQL). When using multiplication with a decimal to get the result precision and scale the following formulas are used:

precision = p1 + p2 + 1 
scale = s1 + s2

For scale, also note the following applies:

In multiplication and division operations, we need precision - scale places to store the integral part of the result. The scale might be reduced using the following rules:

1. The resulting scale is reduced to min(scale, 38 - (precision-scale)) if the integral part is less than 32, because it can't be greater than 38 - (precision-scale). Result might be rounded in this case.
2. The scale won't be changed if it's less than 6 and if the integral part is greater than 32. In this case, overflow error might be raised if it can't fit into decimal(38, scale)
3. The scale will be set to 6 if it's greater than 6 and if the integral part is greater than 32. In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6 decimal places or the overflow error will be thrown if the integral part can't fit into 32 digits.

In this case, the new precision would be 46 and the new scale would be 14. Then we need to apply the integral part retention, as shown above. This results in 46 - 14 = 32. As the value is 32, point is not applied (as it's not less than 32). Point 2 is not applied, again, as the scale is not less than 6. This means that the scale is reduced to 6, as the integral part is so high.

Finally, as 46 is too large for the precision, it is reduced to 38, giving a decimal(38,6).