SQL Server Computed Column

Hello,

This is for many of those who uses Computed Column in SQL server Tables.

It is a really wonderful feature made available by Microsoft in SQL server Tables.

For those of you who are not aware of it I will explain it below,

It is a column in table which computes by itself on the basis of formula given to it. If you are using columns in the formula then that columns should belong to table itself or else it won't work. It will not table data type explicitly but when you see it in design view or view definition of table using alt + F1 it will show data type of the same column as VARCHAR(MAX).

You will find it in design view. Select the column and you will find its property below. Scroll down the properties and you will find a computed column field above Identity Column. Add your formula there and see the magic when you insert records in the table's other columns.

You can even create computed column using script e.g

CREATE TABLE table_name

(

      ID bigint identity(1,1) primary key,

      Name  varchar(100),

      Salarypermonth numeric(18,2),

      Salaryperyear as (Salarypermonth * 12)

)

You don't need to insert value in this column it will automatically inserted and the column can never be null as it will always calculate the value by itself.

Important thing which I figured out last week and which forced me to write this blog is Persisted property of this Computed Column which is very important for those who are using computed column.

If Persisted flag is set to False

• Column will calculate value for the column and store it in the respective column on the Insert Statement on the table or Update Statement on the table.
• But it is not really the case. When you fire Select query on the table the formula again works on so the result can be manipulated at runtime as formula again fires up and you may not get required result.
• Try above case using Getdate() function in the computed column you will not get proper result of the column.

If Persisted Flag is set to True then the data which is stored in the column at the time of Insert or Update Statement will be retrieved as it is with out the formula being fired again at runtime. 

Persisted flag can be set to True only when there is a deterministic value used in you formula. Say tables column is used in the formula like our above example where we are using Salarypermonth column to calculate the value for Salaryperyear column. But if you are using Date funtions then it will not allow you to set to True so please use this really interesting and helpful column very wisely.