Learn about Computed Column in SQL Server

1
1037

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.

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.

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.

 

Expression for Computed column in SQL Server
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.

STEP 5 :-  Read data from Student table again. 

Expression for Computed or Calculated column in SQL Server
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.

Editor – Chander Sharma

View articles written by Chander Sharma

 

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.6/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: +6 (from 6 votes)
Learn about Computed Column in SQL Server, 9.6 out of 10 based on 7 ratings