Computed Column in SQL Server

 

Computed Column is a column generated by a calculation using one or more columns in a table. For example, you have columns A, B and C in the table. If you want the sum of your A and B columns to be in your table as a D column, you must create a computed column in this table.

Example of Computed Column:

Add a computed column to an existing table:

By default, Computed Columns are not stored on the disk unless they are marked as PERSISTED. So they’re actually virtual. Data is automatically calculated when the data is requested.

If it is marked persisted, the calculation and to write to disk operations are performed on each insert operation or when column associated with “computed column” is updated.

In other words, marking as persisted decreases the performance of Insert, Update, and delete. Select performance will increase slightly. You must determine this process according to the needs of your system.

PERSISTED Computed Column:

You cannot insert or update these columns. If we want to insert and update these columns as below, we will get an error.

The error we received when we wanted to do insert;

Msg 271, Level 16, State 1, Line 9

The column “D” cannot be modified because it is either a computed column or is the result of a UNION operator.

If you want to add default constraint to the computed column, the below error will be occured.

Msg 1752, Level 16, State 0, Line 9

Column ‘D’ in table ‘ComputedColumnTest2’ is invalid for creating a default constraint.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint or index. See previous errors.

Below you can find the results of the tests I have done with Computed Column;

• If the Computed Column is defined as PERSISTED, the primary key and the foreign key can be defined.

• You can create index and unique constraint.

• You can create the Computed Column using non-deterministic functions(generating different values each time it is called) such as GETDATE (). But you can’t set PERSISTED, and you can’t create index.

• You cannot insert or update to the computed column.

 

You can read the following articles about the concepts such as primary key, foreign key, unique constraint, default constraint.

What is Primary Key and Foreign Key“,

What is Unique Constraint“,

Differences Between Primary Key and Unique Constaint“,

How To Create Default Constraint On SQL Server

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *