Computed Columns in SQL SERVER

Computed Columns

Are the columns for which values will be automatically computed having Insert /Update?

To create a column as computed column , while creating a table next to the column name use the keyword as followed by the formula based on which value has to be computed for that column.

While inserting rows into the tables having computed columns, you must use the following insert syntax of insert statement by excluding columns.

Insert table name (col, col2) values (va1, val2)

The following example creates a table with name Marks by making total, average and grade as computed columns

Create table marks(sid int, c int, cpp int,sql int, total as c+cpp+sql, Average as (c+cpp+sql)/3.0,

Grade as case

When (c+cpp+sql)/3.0 >=90 then 'Distinction'

When (c+cpp+sql)/3.0 >=70 then 'First Class'

When (c+cpp+sql)/3.0>=55 then 'Second class'

When (c+cpp+sql)/3.0>=35  then 'Third Class'

Else 'Fail'

End)

insert marks(sid,c,cpp,sql) values(100,40,30,20)

insert marks(sid,c,cpp,sql) values(100,40,40,40)

insert marks(sid,c,cpp,sql) values(100,70,70,70)

select * from marks

100 40  30  20  90  30.000000 Fail

100 40  40  40  120      40.000000 Third Class

100 70  70  70  210      70.000000 First Class

Creating Computed Column in GUI

While creating a table in GUI, If you want to make a column as computed column click on column name

Go to column properties available at the bottom Expand the property computed column specification and by using property formula specify the formula based on which value has to been computed for that column and press Enter

.