I hope everyone might have faced this problem.
declare @a numeric(18,2)
declare @b numeric(18,2)
set @a = 10.35
set @b = 20.34
In the above declared variables it is mentioned that precision = 18 and scale = 2, so I am expecting the result of same format. But here SQL server adds more scale while multiplying or dividing.
But these were correct results and the scale value is decided based on the below formula.
Here let’s consider @a has p1 as precision and s1 as scale and @b p2 as precision and s2 as scale
Here p1 and p2 = 18 and both s1 and s2 = 2.
So the precision value would be of
p1 - s1 + s2 + max(6, s1 + p2 + 1) = 18 – 2 + 2 + max(6, 2 + 18 + 1) = 18 + 21 = 39
and scale value would be of
max(6, s1 + p2 + 1) = max(6, 2 + 18 + 1) = 21.
So to avoid more scale value we need to use cast or convert in the result
select cast(@a*@b as numeric(18,2))
select cast(@a/@b as numeric(18,2))