Precision value mismatch while division

Question

SELECT(1663356.000000000000000/133920.000000)

It gives output like: 12.4205197132616487455197132

But while I am doing same operation using Temp tables with precision of (38,15) the output value scale is only 9.i don't no why it's happening?

Example:

create table #Temp
(
  a numeric(38,15),
  b numeric(22,6)
)

insert into #BASELINE values (1663356.000000000000000,133920.000000)

select a/b from #BASELINE

I am getting output like: 12.420519713

You get the same result by doing this: `SELECT CONVERT(NUMERIC(38,15), 1663356)/CONVERT(NUMERIC(22,6), 133920);` — Richard Hansell, Apr 02 '19 at 16:01
Also this: https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy — Richard Hansell, Apr 02 '19 at 16:03

score 0 · Answer 1 · answered Apr 02 '19 at 16:09

So I think the calculation is this:

p1 = 38
s1 = 15
p2 = 22
s2 = 6
result precision = 38 - 15 + 6 + MAX(6, 15 + 22 + 1) = 67
result scale = MAX(6, 15 + 22 + 1) = 38

But then this rule kicks in: "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."

So the "real" scale is 38 - (67 - 38) = 9, which matches your findings.

Precision value mismatch while division

1 Answers1