How To Use Computed Column In SQL Server

Computed columns or Calculated columns in SQL Server as the name suggest, computed based on other column values instead of manual user input. Regular columns are populated by user or external inputs. However, computed columns are derived using existing columns which receive input from a user.

Computed columns do not need any external updates through DML statements or triggers. This helps as the table start keeping track of values in a computed column based on other columns.

For instance, the employee table is storing monthly_salary, first_name and last_name. In case, we need to calculate the yearly salary as well as an employee’s full name. Supposedly, we do not wish to repeat queries for manually calculating yearly salary and full name on the fly. We can solve this problem by creating computed columns.

Computed Columns in SQL Server

1.By default, computed columns are calculated on runtime based on other columns in the same table.

2.Non-Persistent computed columns are not stored thus does not occupy any space on the disk.

3.Persistent computed columns are calculated and stored. Furthermore, occupy disk space as a regular column.

4.Indexing a Persistent column is possible to improve performance.

5.Non-persistent computed column add Compute Scalar operator in execution plan to indicate calculation is performed on the fly.

Sample Table & Data For Demo

Let’s populate some records in the employee table for computed columns’ demo.

DROP TABLE IF EXISTS dbo.Employee;

CREATE TABLE dbo.Employee
(
   ID	           INT		   PRIMARY KEY
  ,First_Name	   VARCHAR(255)	   NOT NULL
  ,Last_Name	   VARCHAR(255)	   NOT NULL
  ,Monthly_Salary  DECIMAL(18,2)   NOT NULL
);

INSERT INTO dbo.Employee VALUES
 (100, 'Roger','Federer'  ,3500)
,(200, 'Novak', 'Djokovic',4000)
,(300, 'Andy', 'Murry'     ,4500)
,(400, 'Rafael', 'Nadal'  ,5000);

SELECT * FROM dbo.Employee;

1.

Standard Computed Column In Query

Following query computes Full_Name and Yearly_Salary on the runtime and shows the result. The only downside, every time we need to include calculation logic for both columns. Although, these two columns won’t take any space in the table as they are calculated on runtime.

--Standard computed columns through query
SELECT Id
      ,First_Name
      ,Last_Name
      ,Monthly_Salary
      ,CONCAT(First_Name, ' ', Last_Name) AS Full_Name
      ,(Monthly_Salary * 12) AS Yearly_Salary 
FROM  dbo.Employee;

2.

Non-Persistent Computed Column

In this example, we are adding two new columns to employee table full_name and yearly_salary. These two new columns will be calculated when they are queried through the SELECT statement. No space is occupied as data is not stored on the disk because they are only calculated by referring first_name, last_name and monthly_salary, existing columns.

Moreover, metadata queries show, columns are computed but not persisted.

--Non-Persistent Computed Columns added to table

ALTER TABLE dbo.Employee DROP COLUMN IF EXISTS Full_Name;
ALTER TABLE dbo.Employee DROP COLUMN IF EXISTS Yearly_Salary;
GO

ALTER TABLE dbo.Employee
ADD   Full_Name As (CONCAT(First_Name, ' ',Last_Name))
GO

ALTER TABLE dbo.Employee
ADD   Yearly_Salary As (Monthly_Salary * 12);
GO

--Table strcuture shows computed yes 

select  t.name as TableName
       ,c.name ColumnName
       ,c.is_computed as IsComputed --1 (Yes)/ 0 (No)
       ,ISNULL(ct.is_persisted,0) as IsPersisted --1 (Yes)/ 0 (No)
from    sys.tables as t INNER JOIN sys.columns as c on	(t.object_id = c.object_id)
	                LEFT JOIN sys.computed_columns as ct on (ct.name = c.name)
where   t.name = 'Employee';

--Non-Persistent computed columns from table structure
SELECT Id
      ,First_Name
      ,Last_Name
      ,Monthly_Salary
      ,Full_Name
      ,Yearly_Salary 
FROM  dbo.Employee;

3.

Persistent Computed Column

By default, computed columns are not materialized. If we make them materialize using PERSISTENT then they will be stored and occupy space. The benefit of making column persistent, the query will run faster as the calculation is already done and the query has stored the result in a computed column.

The following query makes full_name and yearly_salary persistent. Now metadata query also shows both the columns are computed as well as persistent.

--Persistent Computed Columns added to table

ALTER TABLE dbo.Employee DROP COLUMN IF EXISTS Full_Name;
ALTER TABLE dbo.Employee DROP COLUMN IF EXISTS Yearly_Salary;
GO

ALTER TABLE dbo.Employee
ADD   Full_Name As (CONCAT(First_Name, ' ',Last_Name)) PERSISTED
GO

ALTER TABLE dbo.Employee
ADD   Yearly_Salary As (Monthly_Salary * 12) PERSISTED;
GO

--Table strcuture shows computed yes 

select  t.name as TableName
       ,c.name ColumnName
       ,c.is_computed as IsComputed --1 (Yes)/ 0 (No)
       ,ISNULL(ct.is_persisted,0) as IsPersisted --1 (Yes)/ 0 (No)
from    sys.tables as t INNER JOIN sys.columns as c on	(t.object_id = c.object_id)
			LEFT JOIN sys.computed_columns as ct on (ct.name = c.name)
where   t.name = 'Employee';

Summary

This tech-recipes post covers Computed columns in SQL Server. We have learned Non persisted and persisted computed columns. How both works differently and storage space allocation. If you like this article, you may want to read through other learning stuff in database posted on Tech-Recipes Database archives.