If you create a computed column on a table and make it persisted then the column is calculated as the data is entered in to the database and therefore takes up space in the database. The value saved also determines the data type which may benefit from being cast.
If you do not cast the value then the type will be set by implication. i.e. by the result of the calculation.
e.g.
In the following case val3 is the result of val1 / val2 but due to the decimal (20,4) / decimal(20,4) its persisted data type is decimal (38,18). This retains the 20 places to the left of the decimal point but adds the maximum number of decimal places remaining (38 is the max total for both)
CREATE TABLE dbo.T1(
val1 decimal(20, 4) NULL,
val2 decimal(20, 4) NULL,
val3 AS (val1/val2) PERSISTED
) ON PRIMARY
If you don’t need this level of accuracy. E.g. val1 is the total value in money and val2 is the quantity in KG then val3 is the price per KG and you want this accurate to 4 decimal places then simply cast it at design time…
CREATE TABLE dbo.T2(
val1 decimal(20, 4) NULL,
val2 decimal(20, 4) NULL,
val3 AS (CONVERT(decimal(20,4),val1/val2,0)) PERSISTED
) ON PRIMARY
This will give you a saving on space and also a more usable value i nthe column but it does introduce rounding issues so make sure you get enough detail in your cast.
Dave
