SQL 2005+

SQL Server Computed Columns (2)

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.

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