vk-kirov (12/4/2009)
...Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": http://technet.microsoft.com/en-us/library/ms190476.aspx
We have two numbers of type NUMERIC(38,10), so their precision = 38 and scale = 10.
According to the table from the above link, the result precision is: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + 49 = 87.
The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.
But there is also a note:
* 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.
Ok, the result precision (87) is definitely greater than 38, so it was reduced to 38. But why the scale was reduced to 6 – I can't find any explanation :ermm:
Well, if the result precision is 87, and the scale is 49, that's a potential of 49 to the right of the decimal point, leaving (87-49)=38 to the left. Now that's an interesting number. If the note were a hard-and-fast rule (i.e, preserve numbers to the left at all costs), the scale would have to be 0. My guess is that the equations aren't exactly as described in Technet. I expect SQL Server actually does the following process (equivalent to the equations except for cases where resulting precision needs to be reduced to 38):
(p1 = numerator precision,
p2 = denominator precision,
pR = result precision;
equivalent s for scale)
-- min() ensures pR is no greater than 38 as mentioned in the note
-- in our example, it becomes 38
pR = min(p1-s1+s2+max(6,s1+p2+1),38)
-- by subtracting from pR and using max(), we ensure a minimum of 6 digits to the right
-- in our example this becomes max(38-(38-10+10),6) = max(0,6) = 6!
sR = max(pR-(p1-s1+s2),6)
If that's the case, the 6 digits simply comes from their choice of 6 as the minimum scale when precision has to be truncated.