Computed Column in SQL Server

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 A
If MarksObtained >= 60 then B
If MarksObtained >= 40 then C
Else F
 

Example 1:

--------------------------------------

-- Prepare data
--------------------------------------
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL   DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (

     EmployeeID INT
    ,EmployeeName VARCHAR(30)
    ,BasicSalary SMALLINT
    ,DOB DATE
)
GO

INSERT dbo.Employee VALUES

 (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')

SELECT * FROM dbo.Employee

 

--------------------------------------

-- Add computed column
--------------------------------------
ALTER TABLE dbo.Employee
ADD Age AS DATEDIFF(YEAR, DOB, GETDATE())  

SELECT * FROM dbo.Employee

 

 

 

 

You can also explicitly define the data-type of the computed column if you want. You can use CAST/CONVERT to explicitly define the data-type.

--------------------------------------

-- Change data type of computed column

--------------------------------------

ALTER TABLE dbo.Employee DROP COLUMN Age

GO

ALTER TABLE dbo.Employee

ADD Age AS CAST(DATEDIFF(YEAR, DOB, GETDATE()) AS TINYINT)

 

 

Example 2:

--------------------------------------

-- Prepare data

--------------------------------------

IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL   DROP TABLE dbo.Student

GO

CREATE TABLE dbo.Student (

     StudentID INT

    ,StudentName VARCHAR(30)

    ,MarksObtained TINYINT

)

GO

INSERT dbo.Student VALUES

 (1, 'Hari', 74)

,(2, 'John', 80)

,(3, 'Peter', 55)

,(4, 'Ravi', 34)

,(5, 'Max', 67)

SELECT * FROM dbo.Student
 

 

 

 

--------------------------------------

-- Add computed column

--------------------------------------

ALTER TABLE dbo.Student

ADD Grade AS

CASE

   WHEN MarksObtained >= 75 THEN 'A'

   WHEN MarksObtained >= 60 THEN 'B'

   WHEN MarksObtained >= 40 THEN 'C'

   ELSE 'F'

END

GO

   

 

SELECT * FROM dbo.Student

　 

 

Note:
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

• Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.              For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(DD, GETDATE()) cannot be indexed because the value may change in subsequent invocations. 
• A computed column cannot be the target of an INSERT or UPDATE statement.