Computed Column in SQL Server

dbtut February 26, 2019 MSSQL

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:

CREATE TABLE dbo.ComputedColumnTest   
(  
    A int NOT NULL  
  , B int NOT NULL  
  , C int NOT NULL  
  , D AS A + B  
);

CREATE TABLE dbo.ComputedColumnTest

(

A int NOT NULL

, B int NOT NULL

, C int NOT NULL

, D AS A + B

);

Add a computed column to an existing table:

ALTER TABLE dbo.ComputedColumnTest   ADD D AS (A + B);

1	ALTER TABLE dbo.ComputedColumnTest ADD D AS (A + B);

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:

CREATE TABLE dbo.ComputedColumnTest   
(  
    A int NOT NULL  
  , B int NOT NULL  
  , C int NOT NULL  
  , D AS A + B  PERSISTED
);

CREATE TABLE dbo.ComputedColumnTest

(

A int NOT NULL

, B int NOT NULL

, C int NOT NULL

, D AS A + B PERSISTED

);

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;

INSERT INTO dbo.ComputedColumnTest(A,B,C,D) VALUES(1,1,1,1)

1	INSERT INTO dbo.ComputedColumnTest(A,B,C,D) VALUES(1,1,1,1)

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.

ALTER TABLE dbo.ComputedColumnTest ADD CONSTRAINT dc DEFAULT 1 FOR D

1	ALTER TABLE dbo.ComputedColumnTest ADD CONSTRAINT dc DEFAULT 1 FOR D

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“