How are precision and scale calculated when decimals are multiplied?

Question

When decimals are divided, the precision and the scale of the result value are calculated due to this formula:

max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38

max scale = MAX(6, s1 + p2 + 1)

What's the formula when decimals are multiplied?

For example:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN
    DROP TABLE #DataSource;
END;

SELECT CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) AS C1 
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * 100 AS C2
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * CAST(100  AS DECIMAL (3, 0)) AS C3
INTO #DataSource;

EXEC tempdb.dbo.sp_help '#DataSource';

The precision and the scale of column C1 are calculated based on the specified formula:

p1 = 9
p2 = 9
s1 = 0
s2 = 0

max precision = (9 - 0 + 0) + MAX(6, 0 + 9 + 1) --> 19
max scale = MAX(6, 0 + 9 + 1) --> 10

When this result is multiplied by 100 (the value 100 is converted to DECIMAL(3,0)), the result has precision is 23 and scale 10.

If the same formula is applied, the values are as follows:

p1 = 19
p2 = 3
s1 = 10
s2 = 0

max precision = (19 - 10 + 0) + MAX(6, 10 + 3 + 1) --> 9 + 14 = 23
max scale = MAX(6, 10 + 3 + 1) --> 14

So, the precision is correct, but the scale is bigger - 14 instead 10.

Could anyone tell what the formula is for calculating precision and scale when decimals are multiplied?

score 2 · Accepted Answer · edited May 23 '17 at 12:40

I have an answer to a very similar question on StackOverflow that goes over this:

How do I control the datatype of a computed column?

But, to state it more completely here:

I suggest reviewing the source documentation used in the posted linked in the Question as it describes the formulas to use for 6 different operations involving decimals: Precision, Scale, and Length. That chart shows:

Operation: e1 * e2
Result precision: p1 + p2 + 1
Result scale: s1 + s2

In the case of having a DECIMAL(19, 10) * DECIMAL(3, 0), we are starting with the following values:

p1 = 19
p2 =  3

s1 = 10
s2 =  0

Which gives us:

Precision = (19 + 3 + 1) = 23
Scale     = (10 + 0) = 10

And that matches the DECIMAL(23, 10) as reported by SQL Server.

And to make it even clearer, I added a 4th column to the test query to reduce the initial division computation to the resulting DECIMAL(19, 10) so there is no question of the order of the 3 mathematical operations happening in C2 and C3. I also am using the sys.dm_exec_describe_first_result_set DMV so that there is no need to create a temp table :-)

SELECT rs.column_ordinal, rs.name, rs.system_type_name, rs.max_length,
       rs.[precision], rs.[scale]
FROM   sys.dm_exec_describe_first_result_set(N'
SELECT CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) AS C1
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * 100 AS C2
     ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * CAST(100 AS DECIMAL (3, 0)) AS C3
      ,CAST(0.5 AS DECIMAL(19,10)) * CAST(100 AS DECIMAL(3,0)) AS C4
', '', 0) rs

Returns:

column_ordinal    name    system_type_name    max_length    precision    scale
--------------    ----    ----------------    ----------    ---------    -----
1                 C1      decimal(19,10)       9            19           10
2                 C2      decimal(23,10)      13            23           10
3                 C3      decimal(23,10)      13            23           10
4                 C4      decimal(23,10)      13            23           10

The full chart of Precision and Scale calculations from the linked MSDN page above is copied below for convenience:

Operation           Result precision                          Result scale *
---------           -----------------------------------       ------------
e1 + e2             max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

e1 - e2             max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

e1 * e2             p1 + p2 + 1                               s1 + s2

e1 / e2             p1 - s1 + s2 + max(6, s1 + p2 + 1)        max(6, s1 + p2 + 1)

e1 UNION |          max(s1, s2) + max(p1-s1, p2-s2)           max(s1, s2)
  EXCEPT |
 INTERSECT e2 

e1 % e2             min(p1-s1, p2 -s2) + max( s1,s2 )         max(s1, s2)

* The result precision and scale have an absolute maximum of 38.
  When a result precision is greater than 38, the corresponding scale
  is reduced to prevent the integral part of a result from being truncated.

How are precision and scale calculated when decimals are multiplied?

1 Answers1