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.
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.
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.
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.
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.
Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.