SQL Server Computed Columns

A computed column is a virtual column which is used to add a new column to a table with the value derived from the values of other columns in the same table.

Computed column is not physically stored in the table, unless column is marked PERSISTED, and it is persisted only if its expression is deterministic.

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.

Lets take an example of Computed Column in SQL Server.

Computed column when creating a table

First , we create a table named as Salesprofit and add computed column in table.

Create table Salesprofit (

ProdId Int identity(1,1) ,

ProdName Varchar(30),

ActualPrice numeric(9,2) ,

SellPrice numeric(9,2) ,

Profit AS SellPrice - ActualPrice
)

You can also check whether the computed column is created or not on table in object explorer.

Lets insert a record into salesprofit table, SQL Server computes the value for the profit column based on the expression – SellPrice – ActualPrice

insert into salesprofit values ('Spare parts' ,550000,600000)

Lets select the records from salesProfit table.

select * from Salesprofit

As you can see , value for profit column is calculated by computed column automatically.

Drop computed column from a table

you can also drop computed column from a table, using Alter table drop command

ALTER TABLE SalesProfit

DROP COLUMN Profit ;

Alter table Salesprofit add salesdate as getdate() persisted

As you can see, It gives an error of non-deterministic column.

If you remove persisted from query, then it will be created .

Alter table Salesprofit add salesdate as getdate()