Learn about Computed Column in SQL Server

Aim –  This article will enlighten you with the use and need of Computed column is SQL Server. Calculated column means same as Computed column.

Description –  Simply, Computed column is a virtual column that is not physically stored in the table, unless the column is marked Persisted. I understand you might get angry reading this bit confusing explanation. To make you understand this concept, let’s get into depth.

In detail, Computed column is computed from an expression by using other columns in the same table. Expression can be a Non-Computed column name, Constant, Function, any combination of these connected by one or more operators. But, this expression cannot be a Sub-Query. If still you are not clear about this, than practical example is the best way out.

Computed column in SQL Server

STEP 1 :- Create Student table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--create table Use tempdb Go CREATE table Student (Sno int, FirstName Varchar(10), LastName varchar(10), Sub1Marks smallint, Sub2Marks smallint, sub3Marks smallint, TotalMarks as ( Sub1Marks+Sub2Marks+sub3Marks))   -- Computed column Go

STEP 2 :-  Insert data into the table (Student).

Once the table is created, then insert few rows into the table.

NOTE – Don’t forget that the Computed/Calculated column will not take any value as input.  If you pass value to the calculated column, it will throw an error.

1 2 3 4 5 6

--Insert data into the table Insert into student values(1,'Avinash','Reddy',55,60,75), (2,'chander','Sharma',57,66,72), (3,'pinal','Dave',87,76,78), (4,'Vinod','Kumar',66,65,92)

In the above statement, we can clearly see that I didn’t give any value for the column “TotalMarks”. This column is a Computed column and it’s values are recalculated every time they are referenced in a query.

STEP 3 :-  Read the data from Student table

Let’s check how the computed column gets stored after we insert the values in the table Student.

1 2 3

--Read the data from table select * From student

TotalMarks column is a Computed column

In the above screen shot we can clearly observe that the Computed column i.e. “Totalmarks” was filled with values based upon the computed column expression i.e. TotalMarks as (Sub1Marks+Sub2Marks+sub3Marks).

STEP 4 :-  Adding one more Computed column

Now, we are going to add one more Computed column to our existing table.

Once we add this column, again we are going to read the same table “Student” and will see how the newly added column gets populated.

1 2 3

--Adding another computed column Alter table Student Add FullName as (Firstname+''+LastName)

STEP 5 :-  Read data from Student table again. 

1 2 3

--Read the data from table select * From student

FullName is another Computed column

In the above screen shot, we can observe the “FullName” is the newly added Computed column and it gets populated with data as well. With this, we reached at the end of the article on “Computed column in SQL Server“. We at PhpRing appreciates your feedback/suggestions as comments below for providing best tutorials.