I hope everyone might have faced this problem.

*For example:*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.

select @a*@b

**Ans: 210.5190**

select @a/@b

**Ans: 0.50884955**

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))*

Ans: 210.52Ans: 210.52

*select cast(@a/@b as numeric(18,2))*

Ans: 0.51Ans: 0.51

Thanks for this clarification. I did face such issues when I do some calculations manually to validate certain figures. Knowingly or unknowingly, I used to ROUND my values always to required decimals.

ReplyDeleteVaidy