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.
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 tblEmployeeOUTPUT
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 tblSalesOUTPUT