# How are precision and scale calculated when decimals are multiplied?

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?

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.
``````