What is a Computed Column in SQL Server?

Posted on by Ian

In SQL Server, a computed column is a virtual column that uses an expression to calculate its value.

A computed column will typically use data from other columns in its calculation. If data in any of those columns is updated, the computed column will automatically reflect the update in its calculation.

Example of a Computed Column

+------------+-------------+------------+----------------+
| PersonID   | FirstName   | LastName   | FullName       |
|------------+-------------+------------+----------------|
| 1          | Homer       | Seinfeld   | Homer Seinfeld |
| 2          | Bart        | Costanza   | Bart Costanza  |
| 3          | Marge       | Kramer     | Marge Kramer   |
+------------+-------------+------------+----------------+

In this example, the FullName column is a computed column. It concatenates the values from the FirstName and LastName columns to calculate the full name.

Without the computed column, if you wanted to select the full name, you’d need to do something like this:

SELECT FirstName + ' ' + LastName AS FullName
FROM Person;

Or you could use the CONCAT() function, like this:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Person;

Every time you wanted to select a full name from the table, you’d need to include the concatenation in your query.

But by using a computed column like the one above, you could do this:

SELECT FullName
FROM Person;

Benefits of Computed Columns

Computed columns can save time when writing queries, reduce errors, and even improve performance.

Computed columns can be handy if you find yourself repeatedly running the same query over and over again, especially if that query is based on business rules that rarely change.

The computed column in the previous example could save us lots of time in the future, because we won’t need to concatenate the first name and last name every time we want to select somebody’s full name.

Examples of Use Cases

Examples of where a computed column could be useful include:

  • Calculating a person’s age, based on their date of birth in another column
  • Concatenating first name and last name, based on first name being in one column, and last name in another
  • Calculating a product’s inventory value, based on the number of products in stock in one column, and the price in another
  • Calculate a person’s BMI (Body Mass Index), based on their height and weight in another column
  • Creating a “Rank” column that ranks the rows, based on values in other columns
  • Calculating the race times of marathon runners, based on their start time and end time in other columns

Persistence of Computed Columns

A computed column is not physically stored in the table unless it’s marked PERSISTED.

If it’s not a persisted computed column, the computation will occur each time the table is accessed.

If it is a persisted computed column, the computed value is physically stored in the table. Meaning, it doesn’t need to calculate its value every time you run a query. If data is updated in one of the columns for which it derives its value, the computed column’s value is updated and stored in the table. This can help with performance.

Be aware that a computed column can only be persisted if it’s deterministic. If it’s non-deterministic, you’ll get an error if you try to persist it.

A non-deterministic column is one that returns different values even with the same input. For example, if you use the current date in your calculation, the value will be different every day, and it will be considered non-deterministic.

If you’re simply concatenating the first name and last name, then that would be deterministic, and you could persist such a column.

Creating a Computed Column

You can create computed columns from the SSMS GUI or with Transact-SQL.

In SSMS

In SSMS, while in Table Designer, create the column you want to be the computed column (or simply select it if it already exists), then under the Column Properties tab, enter your formula into the Computed Column Specification property field.

Don’t worry about giving the computed column a data type – SQL Server will give it a data type based on your formula.

You can also select Yes for Is Persisted if you wish.

Using T-SQL

See Create a Computed Column in SQL Server using T-SQL for an example of creating a computed column using Transact-SQL.