Post
Ask Question

Computed 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.

  1. create table tblEmployee  
  2. (   
  3. EmpId varchar(10)  
  4. , DOB datetime  
  5. , DOR as (dateadd(yy, 60, DOB)) persisted  
  6. )  
  7. insert into tblEmployee (EmpId, DOB)  
  8. select 'EMP0001', '01/1/1980' union all  
  9. select 'EMP0002', '04/1/1981' union all  
  10. select 'EMP0003', '03/06/1977'  
  11.   
  12. select * from tblEmployee  
  13. drop table tblEmployee  
Output

Output

Example 3: Calculate total (price * qty) using function,
  1. create  
  2. function dbo.udf_CalclateTotal(@price int, @qty int)  
  3. returns int  
  4. as  
  5. begin  
  6. return @price * @qty  
  7. end  
  8. create table tblSales(OrderId int identity, Price int, Qty int, Total as(dbo.udf_CalclateTotal(Price, Qty)))  
  9. insert into tblSales(Price, Qty)  
  10. select 8, 3 union all  
  11. select 5, 5 union all  
  12. select 2, 50  
  13. select * from tblSales  
  14. drop table tblSales  
Output

Output