Sunday, September 12, 2010

Precision and scale values.

Sometimes I wonder why SQL server is not returning the exact number of decimals while multiplying or dividing two variables or columns….!
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.52

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

Ans: 0.51

1 comment:

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

    Vaidy

    ReplyDelete