How do you create Computed/Calculated column
in SQL Server?
A computed column is computed based on an expression that can use other columns of the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators. However, the expression cannot be a sub-query.Scenario: I want to create a computed column which should be calculated based on the values of other columns. For instance, Employee table has DateOfBirth column and I want to create a computed column to store the Age of an employee (in years). Another example could be to display the Grade of a student based on the marks obtained by the student.
Example 1: Employee (EmployeeID,
EmployeeName, BasicSalary, DOB)
EmployeeID
|
EmployeeName
|
BasicSalary
|
DOB
|
1
|
Hari
|
5000
|
1983-05-05
|
2
|
John
|
3500
|
1978-10-25
|
3
|
Peter
|
4500
|
1982-02-10
|
4
|
Ravi
|
2500
|
1980-03-04
|
5
|
Max
|
3600
|
1985-09-20
|
Problem: Add a new computed column
Age in Employee table.
Example 2: Student (StudentID,
StudentName, MarksObtained)
EmployeeID
|
EmployeeName
|
MarksObtained
|
1
|
Hari
|
74
|
2
|
John
|
80
|
3
|
Peter
|
55
|
4
|
Ravi
|
34
|
5
|
Max
|
67
|
Problem: Add a new computed column
Grade in Student table using
following grading system:
If MarksObtained
>= 75 then AIf MarksObtained >= 60 then B
If MarksObtained >= 40 then C
Else F