SQL Server decimal precision issue when multiplying

Scenario: Your client needs 29 significant digits and 10 places of decimal point precision in the data that's stored in your system. So you set up your fields to be decimal(29,10) and go about your merry way. But then you run into this:

Hey, what happened to my ten decimal places? And why is it suddenly able to give me the right answer if I use actual values instead of variables?

The answer, of course, is described here and here. What these posts don't tell you is what to do with your original problem and how to get 10 decimal places in your result. What you have to do is increase the scale of your inputs without increasing the precision, and to determine how much to increase the scale the SQL_VARIANT_PROPERTY function you saw in that second post is going to be your best friend.

SQL_VARIANT_PROPERTY is a function that allows you to inspect the properties of a scalar value, including its datatype and precision. Here I've created a small script with two variables @A and @B, both decimal(29,10). I can call SQL_VARIANT_PROPERTY and pass in the product of @A and @B, and SQL Server will tell me what datatype I can expect in return.

Now all I have to do is ratchet up the scale until I get enough decimal points in my product, and then explicitly cast the two operands to the higher decimal precision when you do your calculation. The magic number, it turns out for me, is decimal(29,16).

Now, you're probably going to say, "Why don't you just store all your values in the database as decimal(29,16) instead of decimal(29,10)?". Well, that's not always going to work. Look at the second part of my UNION statement above. If you have to divide one decimal into another and your precision is 29, you are always going to end up with decimal(38,9) if both numbers have the same precision. What you have to do is set the numerator to a higher scale and leave the denominator alone. This will slowly increase the scale of our answer.

You may also encounter situations where you need to multiply three decimal(29,10) values together. In that situation, your precision and scale for all three numbers is going to be more like (29,21).

UPDATE: One thing to be cautious of: if you're converting a decimal(29,10) to a decimal(29,21), then you're going from 19 digits available left of the decimal point to 8 digits available left of the decimal point. Consequently, if you have values greater than 99,999,999, you will get an arithmetic overflow error when trying to upcast. The best thing to do would be to profile the size of your existing data values and determine if future data values will get much bigger, and then use that as a guide for how much you can upcast (e.g. if your values are in the trillions, then the difference between your precision and scale must be at least 13 digits, so your maximum cast will be to decimal(29,16)).