Computed Columns in SQL Server 2012

Computed Columns:

1.Computed columns are the columns for which value is automatically computed during insert.

2.To make a column as computer column, we have to specify a formula for that column based on which value will be computed for that column by using the keyword "AS".

3.It is not possible to specify datatype for computed columns.

create table student(
sname varchar(20),
sno int,
s1 int,
s2 int,
s3 int,
stot  as s1+S2+s3,
savg as (s1+s2+s3)/3,
sres as case
when (s1+s2+s3)/3 >=90 then 'Distinction'
when (s1+s2+s3)/3 >=70 then 'first class'
when (s1+s2+s3)/3 >=50 then 'second class'
when (s1+s2+s3)/3 >=35 then 'third class'
else 'fail'
end

)

create table student1

(

sidint,

snamevarchar(10),

mathsint,

physicsint,

chemint,

total as (maths+physics+chem)

)

insert into student1 values(100,'srinivas',90,90,90)

select * from student1

update student1

setmaths=100 where chem=90 automatically update the total value

Here whenever we insert student number, student name and three subjects marks it will automatically performs sum, average and computes the class of a student, whenever we update the data automatically these formulas invoke.

Thanks

Srinivas