Tuesday, 16 July 2013

Computed Columns In SQL Server

Today, i would like to talk about Computed Column in SQL Server.

what is computed column ? 
A computed column is computed from an expression that can use other columns in the same table. The expression can be a non computed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a sub query. ( As Per MSDN)

Meaning :  The column which has to be populated while inserting the data to the already existing columns

Example : 

-- creating sample table with Computed Column
CREATE TABLE ComputedColumn (FirstName VARCHAR(50), LastName VARCHAR(50) , FullName AS FirstName + LastName )

with the above script we can create table and the next step is insert the data into table, if you run the following script , you will get an error

INSERT INTO ComputedColumn VALUES ('Narendra' ,'Kotha','NarendraKotha')

because the column "FullName" is not normal column which we are used to insert the data but it is computed column and the data is going to be populate in this column was already computed with the help of already existing columns.

the below script works fine 

INSERT INTO ComputedColumn VALUES ('Narendra' ,'Kotha')

Limitations  :- 
1). we cannot insert the data explicitly into computed column
2). we cannot update the data in computed column
3). we cannot change the datatype of computed column with ALTER
4). Only Unique and Primary Key constraints are allowed create on computed column 

Note : There are so many limitations but the above mentioned are best to my knowledge and i have faced  while working with Computed Columns.