SQL 2005+

SQL Server Computed Columns

by Richard Carr, published at http://www.blackwasp.co.uk/SQLComputedColumns.aspx

The thirteenth part of the SQL Server Programming Fundamentals tutorial investigates computed columns. Values in these columns are calculated automatically by the database management system using an expression that is attached to the column definition.

What is a Computed Column?

A computed column is a column that contains values that are calculated, rather than inserted. When the column is defined, a computed column specification is provided. This definition includes the expression that is used to determine the value of the column in each row of the table. It is not possible to specify the values for these columns in INSERT or SELECT statements.

The expression for a computed column may include the names of other, non-computed columns from the table. These can be combined with literal values using operators. For example, if a table contains UnitCost and UnitsUsed columns, the total cost for each row could be calculated using the formula "UnitCost * UnitsUsed". If a 17.5% tax should be computed as a further column, this could use the expression "UnitCost * UnitsUsed * 0.175". For more complex computations, you can include calls to functions, such as the GetDate function used earlier in the tutorial

Usually the values generated by a computed column are not stored within the database. Instead, the column is virtual, indicating that its values are calculated every time they are required by a query. However, it is possible to specify that a computed column be persisted. A persisted column's values are stored in the table and are recalculated when one of the columns that they reference changes. To mark a column as persisted, it must be deterministic. This means that SQL Server must be able to verify that the column will always produce the same result. A function such as GetDate is not deterministic as its value changes every time the expression is evaluated.

Computed columns can be used in most situations. However, in some cases they must be persisted, such as when they are added to check constraints or foreign keys, which will be discussed later in the tutorial. They must also be persisted if the column is marked as NOT NULL. Computed columns may also be used in primary keys and indexes if the expression is deterministic.

Adding a Computed Column to a Table

There are various ways to add a computed column to a table. We will consider two options: adding computed columns using the SQL Server Management Studio graphical user interface tools and using Transact-SQL statements.

In this article we will add computed column definitions to columns in the JoBS tutorial database. This database has been created in the earlier articles in the tutorial. If you do not have an up-to-date version of the database, download and run the creation script using the link at the start of this page. This will create the database and some sample data. You can also use this script to refresh your copy of the database.

Adding a Computed Column Using SQL Server Management Studio

Whilst first creating a database, computed columns can be added using the table designer in the SQL Server Management Studio (SSMS). A computed column definition can be added to a new column or an existing one. If the table already contains data and an existing column is modified in this manner, all of the data in the column will be removed.

We can demonstrate the creation of a computed column using the Contracts table. This table includes a column named RenewalDate that holds the renewal date, if one exists, for each contract. We can use this value to calculate the number of days left before the contract expires, or the number of days that the contract is overdue. This can be achieved using the GetDate function to determine the current date and the DateDiff function, which calculates the difference between two dates. A detailed description of the DateDiff function will be included in a future article.

To begin, open the table designer for the Contracts table. Add a new column to the table named "DaysUntilRenewal". You do not need to provide a data type for the column as this will be determined automatically by SQL Server and is dependant upon the expression specified in the computed column specification.

With the new row selected, the column properties section will permit editing of the column's settings. In the property list's "Table Designer" section you will find the "Computed Column Specification" section. Click the + icon to the left of this option to expand the property. Two items are now available. The Formula option allows the specification of the expression that will be used to calculate values for the column. The second option, "Is Persisted", allows you to specify whether the column's values will be stored in the database.

The calculation required for the DaysUntilRenewal column is "datediff(d,getdate(),RenewalDate)". This function will calculate the difference between the renewal date and the current date, with the "d" parameter indicating that the result will be provided as a number of days. Add the formula to the appropriate property. This computed column is not deterministic and cannot be persisted so leave this setting as "No". Save the table and close the designer window.

To test the computed column, right-click the Contracts table name and select "Open Table". You should see that the new column is visible and already populated with values. Where a value is positive, this represents the number of days until the contract expires. Negative numbers indicate the number of days that the contract renewal is overdue. Note that the computed column values cannot be modified. Also note that if you change a value in the RenewalDate column the expression is recalculated.

Adding a Computed Column Using Transact-SQL

Computed columns can be added to a table during its creation or at a later time using T-SQL commands. Unlike with the table designer, an existing column cannot be changed from a standard column to a computed version without executing a series of change commands.

Let's start by looking at the use of computed columns when creating a new table. To add the computed column, the formula is used in place of the data type. The syntax for a computed column specification is as follows:

If the column values are to be stored within the database, the PERSISTED keyword should be added to the syntax, as follows:

Using these syntax variations, the Contracts table with the DaysUntilRenewal column could have been created using the following T-SQL:

CREATE TABLE Contracts
(
    ContractNumber INT IDENTITY NOT NULL,
    CustomerAddressId INT NOT NULL,
    RenewalDate SMALLDATETIME,
    RenewAutomatically BIT,
    ContractValue MONEY NOT NULL,
    DaysUntilRenewal AS datediff(d,getdate(),RenewalDate)
    CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED
    (
        ContractNumber
    )
)

To add a computed column to an existing table using T-SQL, the process is similar to adding a standard column; the ALTER TABLE and ADD clauses are combined with the new column specification. Try the following statement to add a computed column to the Customers table. This new column calculates the number of days that each Customer has been registered in the database.

ALTER TABLE Customers ADD Age AS datediff(d,CreatedDate,getdate())

As a final example, execute the statement below. This adds a new computed column to the UsedParts table that calculates the total cost of the parts used for each row. As this calculation is deterministic, it can be persisted.

ALTER TABLE UsedParts ADD TotalCost AS UnitsUsed * UnitCost PERSISTED