Monday, August 30, 2010

Computed Columns

Computed columns in Microsoft SQL Server:


A computed column is based on an expression defined when you create or alter the table, and is not physically stored in the table unless you use the PERSISTED keyword.

A computed column is one defined to be an expression that is executed whenever the column is retrieved. If we use the PERSISTED keyword the expression is being executed while we insert or modify a row.

Syntax:

column_name AS computed_column_expression [ PERSISTED ]


Eg: Non-persisted computed column

CREATE TABLE Student
(
RollNo INT IDENTITY PRIMARY KEY,
Name VARCHAR(25),
Mark1 INT,
Mark2 INT,
Total AS Mark1+Mark2
)


Eg: Persisted computed column

CREATE TABLE Student
(
RollNo INT IDENTITY PRIMARY KEY,
Name VARCHAR(25),
Mark1 INT,
Mark2 INT,
Total AS Mark1+Mark2 PERSISTED
)