Add Computed Column in Table

Wednesday, October 27, 2010

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. See the below example for details.

CREATE TABLE Authors
(
AuthorId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(100),
LastName nvarchar(100),
FullName AS (FirstName + SPACE(1) + LastName) -- computed column
)

The above table sample has the FullName computed column defined as the concatenation of two other column values in the same table.
This is a simple expression sample. We define the computed column by "AS" clause and see that we do not define a column type since the type is defined implicitly by the calculation expression.

INSERT INTO Authors (FirstName, LastName) VALUES (N'Roger', N'Wolter')
INSERT INTO Authors (FirstName, LastName) VALUES (N'Dejan', N'Sarka')
SELECT * FROM Authors

We can add computed column after creating the table. Lets take a example, in the same table, we want to add one more column with CASE expression to define the calculated column value.

ALTER TABLE Authors ADD FullName2 AS (CASE WHEN AuthorId <>

Now insert some data into the sql table, and see the results.

INSERT INTO Authors (FirstName, LastName) VALUES (N'Itzik', N'Ben-Gan')
SELECT * FROM Authors

Note: You can add the same functionality with the temporary table also. i.e. you can add computed column in temporary table at the time of creation (with Create table #tmp (T-SQL command) ) or at the time of alteration/modification.

0 comments:

Post a Comment