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.
