Persisted Computed Column Data Type

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

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s