Monday, April 15, 2013

computed column in sql server

Today, on the post related to "Auto generation of Id" on my blog, 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: 1Auto 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
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

0 comments:

Post a Comment