Introduction

Computed columns are just virtual columns whose content is the result of an expression. Usually, they are used to hold data based on the rest of the columns of the table. The expression can contain other non computed columns of the table, constants, operators and functions, but you cannot specify a query as an expression for a computed column.

As they are “virtual” columns, they are not stored on disk like the rest of the table. In fact, they are not stored but calculated every time the column is accessed in a query. As you will see, you can force SQL Server to store (“persist”) the column in the table with some restrictions.

The best way to understand how computed columns work is by using samples. At the end you will find a file containing all scripts used in the article, and we will show you some of them in the text to illustrate the explanations. To start, we are going to create two tables: the first one to hold invoice information and the other with the detail lines of those invoices. You can also find some inserts in the script file to create sample data.

CREATE TABLE invoices
(   id_invoice    INT PRIMARY KEY IDENTITY
  , customer_name VARCHAR(25));
CREATE TABLE detail_lines
(   id_detail         INT PRIMARY KEY IDENTITY
  , id_invoice_detail INT
  , product           VARCHAR(30)
  , unit_price        MONEY
  , quantity          INT
  , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));

The way to create a computed column is the same way that you would create other columns in a table, with a CREATE TABLE or ALTER TABLE statement. For a computed column, we replace the column data type with the expression that will be used to get the column content. The syntax will be the column name, followed by the key word “as”, and then the expression. Let’s create a computed column in the detail_line table to store the total amount of the line, which we calculate by multiplying unit_price and quantity.

ALTER TABLE detail_lines
 ADD total_amount AS unit_price * quantity;

Check for a Computed Column

There are several ways to confirm that a column is really a computed column. One of them is using the function columnproperty() specifying the property “IsComputed”.

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')

Another way of getting computed columns information is through the system view, sys.computed_columns. This view is an extension of the sys.columns view. This means that sys.computed_columns inherit all the columns from sys.columns view and also add others that are specific to this type of column. Throughout the article we will see some of the columns in this view, as we see different characteristics of the computed columns. For now, is enough to know that this view only shows computed columns and has a column, named is_computed, that tells if the column is computed or not. Obviously, all the records of this view will have a one in this column.

SELECT name, is_computed
 FROM sys.computed_columns;

As the content of the column is calculated every time the column is referenced in a query, the content is always updated. Any change in the columns that are included in the expression, is automatically reflected in the value of the column. We can see this by changing the quantity in a registry in the sample detail_lines table and check the result.

UPDATE  detail_lines
 SET quantity = 4
 WHERE product = 'Cup'
SELECT product, unit_price, quantity, total_amount
 FROM detail_lines 
 WHERE product = 'Cup'

A Step Forward

What we have seen so far with calculated columns is very basic since it only involves performing calculations with other columns. However, the expression of the computed columns may contain functions, both T-SQL standard functions and user-defined functions (UDF). In this way, it is possible to extend the functionality of these columns much further.

Let’s see a sample of this. We are going to add a computed column to the invoices table that computes the total amount of the invoice. To do this, we have to get the invoice number and query the detail_lines table to sum the total_amount from every record with that invoice id. The best way to do this is using a function that receives the invoice id as a parameter and returns the sum. After that, we have to create the column that uses this function.

CREATE FUNCTION fn_total_invoice (@invoice_number INT)
RETURNS MONEY
AS
BEGIN
  DECLARE @total MONEY
  SELECT @total=SUM(total_amount)
   FROM detail_lines 
   WHERE id_invoice_detail = @invoice_number
  RETURN @total
END
ALTER TABLE invoices
 ADD total_invoice AS dbo.fn_total_invoice(id_invoice)

We can verify this column is working correctly by adding a new record in the table, detail_lines, so the total_bill should change.

INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity)
 VALUES (2,'Cup',9.90,1)
SELECT id_invoice, customer_name, total_invoice 
 FROM invoices
 WHERE id_invoice=2

Altering the Column

There could be situations that you have to modify a computed column. Unfortunately, this is not possible. To make that change, it is necessary to delete the column and recreate it with the new expression.

In the case where the computed column uses an external function, we will not be allowed to modify this function. If we try, we receive an error indicating that this function is linked to the table. To change the function, it is necessary to delete the column, perform the modification of the function, and finally, re-create the column with the new version of the function.

We can get the definition of the column at “definition” column from the sys.computed_columns view.

SEECT name, definition
 FROM sys.computed_columns

Storing a Computed Column

As we mentioned before, these columns are “virtual” so they are not physically stored in the table. However, there is the possibility of forcing the calculation to be physically stored in the table, which is called “persist”ing the column. This can improve the performance with SELECT statements since this avoids having to perform the calculation of the column every time it is referenced.

In addition, in order to persist the column, the expression used to create the column have to be a “deterministic” one. As we can see at Microsoft website, “deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.” (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). If we want to know if SQL Server considers the expression of a computed column as deterministic or not, we can use the columnproperty() function with the “IsDeterministic” property.

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')

If the definition of the column is a user defined function, you can also verify whether that function itself is deterministic or not. To do this, you have to use the objectproperty() function with the IsDeterministic property.

SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')

As you can see from the queries, the column of the first example, in which we calculate the total price of the detail, is considered deterministic. However, the function that calculates the total price of the bill is considered non-deterministic. In this way, only the column total_price of the detail_table table can be stored in the table.

ALTER TABLE detail_lines DROP COLUMN total_amount;
ALTER TABLE detail_lines
 ADD total_amount AS unit_price * quantity PERSISTED;

Again in the sys.computed_columns view you can see the field is_persisted, which will indicate if the column is persisted or not in the table.

Indexes with Computed Columns

It is possible to use computed columns in indexes, although they must meet several requirements:

• Ownership: All the functions that are used in the definition of the computed column must be owned by the same user as the table.
• Determinism: The computed column must be deterministic. Also, if the column contains CLR expressions, in addition to being deterministic, the column must be persisted.
• Accuracy: The expression of the calculated column must be precise. This implies that it can not be of the “float” or “real” data type. Nor can you use this type of data in your definition. This feature can be verified with the columnproperty() function by specifying the IsPrecise property.
• Data type: The computed column can not be of the types text, ntext or image. Also, if the expression contains image, ntext, text, varchar (max), nvarchar (max), varbinary (max), or xml data types, it can be used only if the data type resulting from the expression is allowed in an index.

In addition to these considerations, the connections used to create the column and the one used to create the index must have certain configurations in order to carry out these actions.

The connection to create the computed column must have the ANSI_NULLS option active. This can be verified with the columnproperty() function, by specifying the IsAnsiNullsOn property.

The connection to create the index is, as well as connections to perform insert, update and delete of records that influence the index must have the options ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER active. Additionally, the NUMERIC_ROUNDABORT option must be disabled.

Last Considerations

To finish, we are going to review some additional aspects that it is necessary to know for the correct use of the computed columns.

Obviously, computed columns cannot be updated, nor included in the values list of an INSERT action. Although, the computed columns can be part of the list of results of a select statement, they can also be used in the clauses WHERE, ORDER BY, or in all those in which an expression can be put.

SELECT product, unit_price, quantity, total_amount
 FROM detail_lines 
 WHERE total_amount > 10
 ORDER BY total_amount

Despite the above, a computed column can not be used in DEFAULT or FOREIGN KEY constraint definition. Neither can be with a NOT NULL constraint definition.

On the other hand, computed columns can be used as part of PRIMARY KEY or UNIQUE constraints. To do this, the definition of computed column should be a deterministic expression.

Conclusion

The use of computed columns can be very useful in some situations. You have to study carefully where to use them, because of the restrictions they have, specially to create index and persist them. This is just the beginning. Feel free to try new things and experiment with computed columns to find new possibilities.