Computed Column In SQL Server

In this blog you will learn how to compute column in SQL Server.

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:

1. Persisted Columns: Persisted columns values are calculated at the time of insertion and update (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.
   
2. 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 of Computed column

Example 1: 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
drop table 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
drop table tblSales

Output