December 24, 2008

SQL SERVER: How to Add Computed Column

Today I came across scenario where I need to add one computed column. Computed column means we can manipulate one or more column from the same table and we can use this column in queries as regular column.

Lets try it:

Create One Table: tblTestComputed

CREATE TABLE tblTestComputed(
FirstName VARCHAR(50),
LastName VARCHAR(50)
)

Lets insert some data into it:

INSERT INTO tblTestComputed(FirstName, LastName)
SELECT 'Tejas', 'Shah'
UNION
SELECT 'Hiral', 'Shah'

So now I run:

SELECT * FROM tblTestComputed

I will get output like:

Add Computed column

Now, I need display name as "Shah Tejas", "Shah Hiral" like that. so what I did is, I added one new column as:


ALTER TABLE tblTestComputed
ADD FullName AS (ISNULL(LastName,'') + ' ' + ISNULL(FirstName,''))

So, now if I run:

SELECT * FROM tblTestComputed

So, Output like:

Add computed Columns

So, I can use this new column "FullName" everywhere to display Full Name.

Computed Column is auto updated, means if FirstName OR lastName is changed then content of this column is also changed accordingly

One more thing We can not update computed column as it computed by other columns

Reference: Tejas Shah (http://www.SQLYoga.com)