In this article, we will explore computed columns in SQL Server and their configurations.
Introduction
Suppose you have an [Orders] table and it holds the information about orders placed by the customers for an online shopping portal.
You have different tax slabs for the different products, and you require a column in your table that has the amount for each order after adding required taxes. In this case, you can utilize computed columns in SQL Server.
Similarly, in another example of [Employee] table, we have a DOB for each employee. We need a computed column to show [Age] of the employee.
A computed column in SQL Server is a virtual column that computes its values from an expression. We can use a constant value, function, value derived from other columns, non-computed column name, or their combinations.
SQL Server does not store these virtual columns physically, so it does not require any storage. We can store these columns physically as well using PERSISTED property if required. If we mark a computed column as persisted, we can define constraints such as Check, Not NULL, or Foreign key. It also allows you to define an index for the persisted computed column.
Let’s create a new table using the SSMS table designer wizard. Expand Databases -> right-click on Tables and navigate to New -> Table:
In the above image, the table shows multiple columns and their data types. We have defined a primary key and identity column on the [EmpID] column.
Add a new column [Age]. In this column, do not select the data type. SQL Server automatically assigns an appropriate data type depending upon the columns for the computed column in SQL Server value.
In the Computed Column Specification, specify the formula for computed column value. In my example [Age] column, we use the DATEDIFF() function to calculate the employee age.
Formula: datediff(year,DOB,getdate())
We have a property in the Computed Column Specification for the persisted column – Is Persisted. Currently, we leave it as default as not persisted.
Save the table and give it an appropriate name.
Click OK, and it creates the table with the computed column. Expand the [Employee] table, and you can see that it shows the Computed keyword in place of a data type.
Now, insert a few records in the [Employee] table. You can note here that we do not insert data in the [Age] column.
|
1 2 3 |
Insert into [SQLShack].[dbo].[Employee] (EmpID,EmpName,DOB) values(1,'Raj','1987-01-01') Insert into [SQLShack].[dbo].[Employee] (EmpID,EmpName,DOB) values(2,'Mohan','1985-06-07') Insert into [SQLShack].[dbo].[Employee] (EmpID,EmpName,DOB) values(3,'Ram','1983-09-01') |
Once we retrieve the records from the [Employee] table, it shows the data for the [Age] column as well.
|
1 2 3 4 5 |
SELECT [EmpID], [EmpName], [DOB], [Age] FROM [SQLShack].[dbo].[Employee]; |
If we update the records in the SQL table, and computed columns calculate updated value once we retrieve data again. However, we cannot update or insert values in the virtual computed columns in SQL Server.
Previously, we used the SSMS table designer to define a computed table. We can create a new table using the following CREATE TABLE script. You can look at the [Age] column that has the formula to calculate its values.
|
1 2 3 4 5 6 7 |
CREATE TABLE [dbo].[Employee] ([EmpID] [INT] NOT NULL, [EmpName] [VARCHAR](50) NOT NULL, [DOB] [DATE] NOT NULL, [Age] AS (DATEDIFF(yy, [DOB], GETDATE())), CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED([EmpID] ASC) ); |
Similarly, we can add a new column into the existing table with the ALTER TABLE statement. In this query, we calculate the retirement date for the employees. You can notice here that we did not specify a data type for the additional column [RetirementDate].
|
1 2 |
ALTER TABLE [dbo].[Employee] ADD [RetirementDate] AS (DATEADD(year,60, [DOB])); |
With the existing data, we can see the employee retirement date calculated by the computed columns.
Use the User-defined Function in the Computed Column
Previously, we specified the formula for computed column calculation. We can use user-defined functions as well in the computed column.
In the below query, we create a user-defined function to calculate the employee retirement date. This function calculates employees’ retirement date as per employee ID.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE FUNCTION fn_employee_promotion (@empid INT ) RETURNS DATE WITH SCHEMABINDING AS BEGIN DECLARE @newdate DATE; SELECT @newdate = DATEADD(year, 60, [DOB]) FROM [dbo].[Employee] WHERE EmpID = @empid; RETURN @newdate; END; |
Before we apply this function in the computed column, let’s drop the existing column using the following ALTER TABLE query.
|
1 |
ALTER TABLE [Employee] DROP COLUMN [RetirementDate]; |
Now, let’s add a new computed column, and you can see we use the user-defined function instead of the computed column formula.
|
1 2 |
ALTER TABLE [dbo].[Employee] ADD [RetirementDate] AS dbo.fn_employee_promotion([EmpID]) |
We can query the [Employee] table, and it returns the output similar to specify formula directly in the computed column.
Physically storing a computed column in SQL Server
As we discussed earlier, a computed column is a virtual column, and it does not get stored in the database. SQL Server calculates its value during runtime.
For this section, let’s create a new [Customer] table with the below script.
|
1 2 3 4 5 6 |
CREATE TABLE Customer ( customerid INT PRIMARY KEY IDENTITY, first_name NVARCHAR(100) NOT NULL, last_name NVARCHAR(100) NOT NULL, ); |
To prove the above point, we insert sample data records in the customer’s table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @i INT= 15000; WHILE(@i > 1) BEGIN INSERT INTO Customer (first_name, last_name ) SELECT name, type_desc FROM sys.objects; SET @i = @i - 1; END; |
Once the above script completes, check the space used by the customer table using the sp_spaceused system stored procedure. It also shows the number of records in the table. We require multiple records to feel the difference in the computed column as a virtual column or persisted column.
|
1 |
sp_spaceused 'Customer' |
Now, let’s add the computed column to get the full customer name and check the space used again. You can also note that it quickly adds the computed column irrespective of the number of rows in the table.
|
1 2 |
ALTER TABLE Customer ADD fullname AS (first_name + ' ' + last_name); |
Now, drop this computed column and add it again with PERSISTED property. It takes approximately 2 minutes in script execution for me.
|
1 2 3 4 5 |
ALTER TABLE Customer DROP COLUMN fullname; Set QUOTED_IDENTIFIER ON ALTER TABLE Customer ADD fullname AS (first_name + ' ' + last_name) PERSISTED; |
Let’s verify the space used by the customer table again. You can see data, and index size increased in Persisted computed column.
To make a computed column as persisted, it should be deterministic. We always get the same value provided we supply specific values in a deterministic function. It should have the same database as well.
For example, a deterministic sum or avg function always returns the same value for specific inputs, but a non-deterministic GETDATE() function always returns a different value.
You can refer to Microsoft’s article Deterministic and Non-deterministic Functions for more details.
In the previous examples, we used the GETDATE() function to calculate the value of [Age] computed column. We cannot use this function directly because it does not satisfy the condition of a deterministic column.
If you try to add a column with a non-deterministic function such as GETDATE(), you get the following error message. It gives the message that we cannot add the column because it is non-deterministic.
|
1 2 |
ALTER TABLE Customer ADD CurrentDate AS (getdate()) PERSISTED; |
As stated earlier, we can use the user-defined function as well in computed columns in SQL Server. You might also get an error message for the non-deterministic function while adding it in the computed column. You should specify WITH DETERMINISTIC clause in the function to avoid this issue.
You might have a question here, should we add a column as persisted or virtual? We should add a virtual computed column in case we have a small number of rows. In the case of a large number of rows, we should filter the records with the WHERE clause. SQL Server calculates the virtual computed column at run time so that you may face performance issues.
Once you create a deterministic persisted computed column, it gives you additional flexibility. You can create indexes to optimize your queries and get an optimized execution plan. You can also create constraints such as Null, Check, Foreign key for persisted computed column.
You should refer to create indexes on SQL Server computed columns to explore indexing on computed columns in SQL Server.
Get a list of computed columns in a SQL Server database
We can use the system function sys.computed_columns and join it with the sys.objects to get a list of available computed columns, their data type, and the column definition (formula). Let’s run this script in the [AdventureWorks] sample database, and you get a list of all computed columns in SQL Server.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT SCHEMA_NAME(o.schema_id) AS schema_name, c.name AS column_name, OBJECT_NAME(c.object_id) AS table_name, TYPE_NAME(user_type_id) AS data_type, definition FROM sys.computed_columns c JOIN sys.objects o ON o.object_id = c.object_id ORDER BY schema_name, table_name, column_id; |
It gives you enough information with examples to start exploring the computed columns in SQL Server.
Conclusion
In this article, we explored computed columns in SQL Server along with its virtual and persisted property. You should analyze your requirement and plan whether you require a persisted column or not. You can also create indexes on these columns for improving query performance.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023