Rajendra Gupta
computed column in SQL Server

An overview of computed columns in SQL Server

June 9, 2020 by

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:

Create a 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.

computed column in SQL Server

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.

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

Computed column

Now, insert a few records in the [Employee] table. You can note here that we do not insert data in the [Age] column.

Once we retrieve the records from the [Employee] table, it shows the data for the [Age] column as well.

View data in Computed column

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.

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

With the existing data, we can see the employee retirement date calculated by the computed columns.

Add a new computed column

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.

Before we apply this function in the computed column, let’s drop the existing column using the following ALTER TABLE query.

Now, let’s add a new computed column, and you can see we use the user-defined function instead of the computed column formula.

We can query the [Employee] table, and it returns the output similar to specify formula directly in the computed column.

Use User-defined Function

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.

To prove the above point, we insert sample data records in the customer’s table.

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.

Space used for computed column

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.

View space usage

Now, drop this computed column and add it again with PERSISTED property. It takes approximately 2 minutes in script execution for me.

Let’s verify the space used by the customer table again. You can see data, and index size increased in Persisted computed column.

Compare space requirements

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.

Persisted column

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.

It gives you enough information with examples to start exploring the computed columns in SQL Server.

Get computed column lists

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
Development, T-SQL

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

69,543 Views