How do I control the datatype of a computed column?

Question

Using SQL Server 2012...

I have two columns:

Price [decimal(28,12)]
OustandingShares [decimal(38,3)] -- The 38 is overkill but alas, not my call.

When I do an ALTER TABLE I get a resulting computed column as a [decimal(38,6)]. I need the datatype to be [decimal(28,12)].

ALTER TABLE [xyz].MyTable
ADD Mv AS OustandingShares * Price

How can I effectively get 12 decimals of scale on this computed column? I've tried doing convert on the OutstandingShares to 12 decimal places as well as wrapping a convert around the OutstandingShares * Price. The only thing I get is a computed field at [decimal(28,12)] with six trailing zeros.

Thoughts?

Answer 1

The Fix

This does what you want:

CONVERT(DECIMAL(28,12), (
                            CONVERT(DECIMAL(15, 3), [OustandingShares])
                          * CONVERT(DECIMAL(24, 12), [Price])
                         )
       )

Test with this:

SELECT CONVERT(DECIMAL(28,12),
                 (CONVERT(DECIMAL(24,12), 5304.987781883689)
               * CONVERT(DECIMAL(15,3), 3510.88)));

Result:

18625175.503659806036

The Reason

The computation is being truncated due to SQL Server's rules for how to handle Precision and Scale across various operations. These rules are detailed in the MSDN page for Precision, Scale, and Length. The details we are interested in for this case are:

• Operation: e1 * e2
• Result precision: p1 + p2 + 1
• Result scale *: s1 + s2

Here the datatypes in play are:

• DECIMAL(28, 12)
• DECIMAL(38, 3)

This should result in:

• Precision = (28 + 38 + 1) = 67
• Scale = 15

But the max length of the DECIMAL type is 38. So what gives? We now need to notice that there was a footnote attached to the "Result scale" calculation, being:

* 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 it seems that in order to get the Precision back down to 38 it chopped off 9 decimal places.

And this is why my proposed fix works. I kept the "Scale" values the same as we don't want to truncate going in and expanding them serves no purpose as SQL Server will expand the Scale as appropriate. The key is in reducing the Precision so that the truncation would be non-existent or at least minimal.

With DECIMAL(15, 3) and DECIMAL(24, 12) we should get:

• Precision = (15 + 24 + 1) = 40
• Scale = 15

40 is over the limit so reduce by 2 to get down to 38, which means reduce the Scale by 2 leaving us with a true "Result Scale" of 13, which is 1 more than we need and will even be seeing.

Answer 2

Use cast() or convert(). Something like:

ALTER TABLE [xyz].MyTable ADD Mv AS cast(OustandingShares * Price as decimal(12, 6)

or whatever type you want it to be.

EDIT:

Oh, I think I'm getting the idea. The problem is the calculation itself. In that case, do the conversion before the multiplication, so you don't have to depend on SQL Server's (arcane) rules for conforming decimal types.

ALTER TABLE [xyz].MyTable
    ADD Mv AS cast(OustandingShares as decimal(28, 12) * cast(Price as decimal(28, 12))

I believe what is happening in your case is that the maximum precision on the calculated result exceeds the allowed thresholds, so the scale is reduced accordingly. This is explained at the bottom of this page.