Code, Microsoft, Performance, SQL, SQL Server, T-SQL, Technical

Computed Columns in SQL Server 2005

“Computed Columns” as the name suggests allows you to set the value of a column based on other columns data. Here’s how you could specify the formula in T-SQL:

--Create Table
CREATE TABLE T1 (
    a INT, 
    b INT, 
    operator CHAR,
    c AS CASE operator
        WHEN '+' THEN a+b
        WHEN '-' THEN a-b
        ELSE a*b
    END
    PERSISTED
) ;

--Insert dummy data into it
INSERT INTO T1 VALUES(1,2,'+')
INSERT INTO T1 VALUES(5,3,'-')
INSERT INTO T1 VALUES(4,4,'')

--View the results
SELECT * FROM T1

Notice the keyword PERSISTED. This will enable the computed column value to be stored physically on to the disk so that it need not be created every time you ask for it. Creating an index on this computed column will improve performance by doing a seek rather than a table scan.

If you want to specify the above computed column in SQL Server management studio directly you can do this:

Modify the table. Go to the computed column specification for the specific column. Expand it and type the following in the “Formula” field:

(CASE [operator] WHEN '+' THEN [a]+[b] WHEN '-' THEN [a]-[b]

ELSE [a]*[b] END)

Enter without line breaks. If you want the column values to be persisted in the storage medium, just change the value of “Is Persisted” to “Yes”

Advertisements

4 thoughts on “Computed Columns in SQL Server 2005

  1. I want to know that how to prepare a computed column in a table in sql server 2005 in which the computed column name is total balance which will be updated automatically by adding the value of total balance and monthly balance. The monthly balance is another column which will be updated by the user.

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