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:

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

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.

1:  p1 - s1 + s2 + max(6, s1 + p2 + 1)
 =  38 - 10 + 10 + max(6, 10 + 38 + 1) 
 =       38      + max(6, 49)
 =       38      +      6
 =       38                 *(applying the rule of absolute 38 max)

The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.

2: max(6, s1 + p2 + 1)
 = max(6, 10 + 38 + 1)
 = max(6, 49)
 =      6 

RBarryYoung (12/9/2009)

---

As for the last part, you appaerntly overlooked applying the MAX(..) function in your calculations:

should be:

1:  p1 - s1 + s2 + max(6, s1 + p2 + 1)
 =  38 - 10 + 10 + max(6, 10 + 38 + 1) 
 =       38      + max(6, 49)
 =       38      +      6
 =       38                 *(applying the rule of absolute 38 max)

and:
should be:

2: max(6, s1 + p2 + 1)
 = max(6, 10 + 38 + 1)
 = max(6, 49)
 =      6 

sknox (12/9/2009)

---

I beg to differ, RBarry. MAX() returns the maximum of the two values. So MAX(6,49) is 49.

RBarryYoung (12/9/2009)

---

Steve: (RE: SSC new site features/wishlist)

This is a perfect example of a thread or post that I would like to be able to tag or remember somehow. Either by adding it to my briefcase, or adding a personal-tag or a public-tag or a special personal file-cabinet or something like that...

surds

chriscoates (12/10/2009)

---

Thanks sknox for some good explanations here. To check I've got this straight ...

The internal casts give us 10 decimal places.
That effectively boils down to:

SELECT ( 1.6757400000 / 10000.0000000000)

For the various reasons described above, SQL decides to truncate this to a 6 decimal place number: 0.000167

The outer cast happens after the division, turning this into a ten decimal place number: 0.0001670000

For the record, I think that's a bit pants. Not what I would have expected.

Good QotD. I've learnt something this morning! I'll definitley have to remember this one. My company does a lot of financial transactions, and we always explicitly cast numbers in an effort to retain lots of decimal places, not lose them. We have come across numbers not adding up correctly before, but were generally unable to track the root cause. I've quite often seen numbers multiplied by a factor so that we work with larger numbers. For example, pounds and pence displayed to 6 decimal places are often stored as decimal pence, rather than decimal pounds.