Computed Column in SQL Server

Today, on one of my post related to Auto generation of Id, a reader asked the meaning of PERSISTED PRIMARY KEY that is related with Computed Column. I answered the question on the post itself, but it gave me an idea to have a detailed post on computed column on my blog.

Computed Column is the column whose values are generated with an expression. An expression can be a non-computed column name, constant, function and combination of any of these connected by one or more operators.
Computed columns can be used in select list, where clause, order by clause.

There are two types of computed column.

• Persisted Columns: Persisted columns values are calculated at the time of insertion and updation (when any columns that is part of their calculation changes) and are physically stored in the table. Indexes can be created on persisted columns as they are physically stored.
• Non-Persisted Columns: Non-persisted columns are calculated on run-time every time they are referenced in a query. Indexes cannot be created on Non-persisted columns as they are not physically stored.

Some examples of Computed column

Example 1: Auto generation of Id 

Example 2:  Calculate Date of Retirement 
Date of retirement is dependent on column Date of Birth as Date of retirement is calculated as (DOB + 60 years), so we are creating DOR as computed column to compute the same

create table tblEmployee(     
    EmpId    varchar(10)
    , DOB    datetime
    , DOR    as (dateadd(yy, 60, DOB)) persisted
)
insert into tblEmployee (EmpId, DOB)
select 'EMP0001', '01/1/1980' union all
select 'EMP0002', '04/1/1981' union all
select 'EMP0003', '03/06/1977'

select * from tblEmployee

OUTPUT

Example 3: Calculate total (price * qty) using function

create function dbo.udf_CalclateTotal (@price int, @qty int)
returns int
as
begin
    return @price*@qty
end

create table tblSales(
      OrderId    int identity   
      , Price    int
      , Qty      int
      , Total as (dbo.udf_CalclateTotal(Price, Qty))
)

insert into tblSales (Price, Qty)
select 8, 3 union all
select 5, 5 union all
select 2, 50

select * from tblSales

OUTPUT