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.
- Whenever we create the table.
- 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.
- Persisted computed column.
- Non–persisted computed columns.
Persisted Computed columns
A persisted computed field occupies memory for the data. Persisted computed columns are run whenever data is inserted or updated in a table. Compared 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 it is selected. Comparisons to non-persisted computed columns are 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 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 296 KB when the column is non-persisted.
Performance
![]()
Limitations of computed columns
- 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 accessed this column.
- You can create persisted computed columns in SQL Server 2000.
- You cannot reference columns from another table.
- You cannot insert or update operations on a computed column.
- You cannot change an existing computed column definition using an altered statement.
Conclusion
This article taught us about Computed Columns with code examples and Limitations in SQL Server.