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.

#development #t-sql #sql