Precision of decimal computed columns in T-SQL

Question

We used to have the following field in database:

FieldA    decimal(19, 2)

We have changed the field to a computed field:

ALTER TABLE MyTable
ADD FieldA AS FieldB * FieldC

Where:

FieldB    decimal(19, 2)
FieldC    decimal(19, 5)

The resulting new FieldA:

FieldA    (Computed, decimal(38,6))

The legacy code uses FieldAwith the assumption that it has two decimals. Thus, the string translations do not apply formatting. As a result, with the new definition, FieldA is shown with six decimals, which is unacceptable.

Is it possible to modify the precision of the computed column (say, to keep the original type decimal(19, 2)) or do we need to add appropriate formatting to all the places that are displaying the value?

Rounding does not work:

ADD FieldA AS ROUND(FieldB * FieldC, 2)

`ROUND()` does not change the result data type. use `CAST() or CONVERT()` like `CONVERT(decimal(19,2), FieldB * FieldC)` — Squirrel, Nov 19 '19 at 05:35

gotqn · Accepted Answer · 2019-11-19T06:06:18.303

Try this:

CREATE TABLE [dbo].[TEST]
(
    [A] DECIMAL(19,2)
   ,[B] DECIMAL(19,5)
   ,[C] AS [A] * [B]
   ,[D] AS CAST([A] * [B] AS DECIMAL(19,2))
);

GO

SELECT *
FROM [sys].[columns]
WHERE [object_id] = OBJECT_ID('[dbo].[TEST]');

Why? Check the formula of how decimal precision is set when operations are perform over decimal values.

So, we have:

[A] DECIMAL(19,2)
[B] DECIMAL(19,5)
[C] AS [A] * [B]

and the formula is:

p1 + p2 + 1 => 2 + 5 + 1 => 7
s1 + s2 => 19 + 19 = > 38

but:

In multiplication and division operations, we need precision - scale places to store the integral part of the result. The scale might be reduced using the following rules:

The resulting scale is reduced to min(scale, 38 - (precision-scale)) if the integral part is less than 32, because it can't be greater than 38 - (precision-scale). Result might be rounded in this case.

The scale won't be changed if it's less than 6 and if the integral part is greater than 32. In this case, overflow error might be raised if it can't fit into decimal(38, scale)

The scale will be set to 6 if it's greater than 6 and if the integral part is greater than 32. In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6 decimal places or the overflow error will be thrown if the integral part can't fit into 32 digits.

and from rule 3 the scale is capped to 6 and you get DECIMAL(38,6). That's why you need to explicitly cast the result to your target decimal type.

Precision of decimal computed columns in T-SQL

1 Answers1