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 AIf 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.