Computed Columns in SQL Server

Computed columns are derived columns based on the other existing columns. Computed columns are a data type that can be created based on the situation.

We can create a computed column at either of the following two levels:

1. Whenever we create the table.
2. By using an alter statement.

First Way (using create table)

create table Employee
(

 FirstName varchar(100),
 LastName varchar(100),
 FullName as (FirstName+LastName)

)

I have created the Employee table with FirstName, LastName and FullName. Here FullName is a computed column since it depends on FirstName and LastName.

insert into Employee (FirstName,LastName) values('Rakesh','Kalluri')
insert into Employee (FirstName,LastName) values('Rama','Kalluri')
insert into Employee (FirstName,LastName) values('Naresh','Chitanipu')
insert into Employee (FirstName,LastName) values('Lohith','Talluri')

In the preceding insert query I have not supplied a value for the FullName column, because it is a computed column automatically generated by the values when the data is stored.

select * from Employee

insert into Employee (FirstName,LastName,FullName) values('Lohith','Talluri','LohithTalluri')

In the above insert query I am trying to insert a value to the FullName column but it gives an error because:

The column "FullName" cannot be modified because it is either a computed column or is the result of a UNION operator.

Second Way (using alter statement)

alter table Employee add FullName as (FirstName+LastName)

There are two types of computed columns.

1. Persisted computed column.
2. Non –persisted computed columns

Persisted Computed columns

Persisted computed columns are run whenever data is inserted or is updated in a table. A persisted computed field occupies memory for the data. Comparing to a persisted computed column is faster than a non-persisted computed column.

First Way

create table Employee
(

 FirstName varchar(100),
 LastName varchar(100),
 FullName as (FirstName+LastName) persisted

)

Second Way

alter table Employee add FullName as (FirstName+LastName) persisted

Memory Usage

declare @start int=1
while(@start<10000)
begin
 insert into Employee (FirstName,LastName) values('Rakesh','Kalluri')
set @start=@start+1
end

I have inserted 10000 records using the preceding query. Then checked how much memory was consumed for the Employee table as in the following:

sp_spaceused Employee

If we observe, the preceding table occupies 448 KB when the column is persisted.



Performance

Non-Persisted Computed columns

Non-Persisted computed columns are run whenever data is selected from a table. A Non-Persisted computed field does not occupy memory for the data, because it is executed when the data is selected. Comparions to non-persisted computed columns is slower than persisted computed columns.

First Way

create table Employee
(

 FirstName varchar(100),
 LastName varchar(100),
 FullName as (FirstName+LastName)

)

Second Way

alter table Employee add FullName as (FirstName+LastName)

Memory Usage

declare @start int=1
while(@start<10000)
begin
insert into Employee (FirstName,LastName) values('Rakesh','Kalluri')
set @start=@start+1
end

I have inserted 10000 records using the receding query. Then checked how much memory was consumed for the Employee table as in the following:

sp_spaceused Employee



If we observe, preceding table occupies 296 KB when the column is non-persisted.

Performance




Limitations:

1. You cannot drop a dependent column whenever a computed column exists in a table.
   alter table Employee drop column FirstName
   Whenever the preceding query is run the following error will occur:
   ALTER TABLE DROP COLUMN FirstName failed because one or more objects access this column.
2. You can create persisted computed columns in SQL Server 2000.
3. You cannot reference columns from an other table.
4. You cannot do insert or update operations on a computed column.
5. You cannot change an existing computed column definition using an alter statement.