h1

How to Add Computed Column – SQL SERVER 2005

12/24/2008

Today i come with scenario in which 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


About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: