Computed Columns

A computed column is an exciting new feature in SQL Server 7.0. A computed column does not store computed data—rather, it stores the expression used to compute the data. For example, I might create a computed column on my table called Total with an expression of Total AS price * quantity.

The expressions you store can be created from a noncomputed column in the same table, constants, functions, variables, and even names. Your computed column will automatically compute the data when it is called in a SELECT, WHERE, and ORDER BY clauses of a query (which you examine on Day 11, "Retrieving Data with Queries"). They can also be used with regular expressions.

There are a few rules to follow when you are dealing with computed columns:

  • Columns referenced in the computed-column expression must be in the same table.
  • The computed-column expression cannot contain a subquery.
  • Computed columns cannot be used as any part of keys or indexes; this includes Fkeys, Pkeys, and unique indexes.
  • A computed column cannot have a DEFAULT constraint attached to it.
  • Computed columns cannot receive INSERT or UPDATE statements.

Here are a couple of examples using computed columns:

CREATE TABLE tblOrder (
OrdID int NOT NULL,
Price money NOT NULL,
Qty smallint NOT NULL,
Total AS Price * Qty
)

This first table has a computed column called Total, which is made up of the price * qty fields.

CREATE TABLE tblPrintInvoice (
InvoiceID int NOT NULL,
InvDate datetime NOT NULL,
PrintDate AS DateAdd(day,30, InvDate)
)

In this example, you have a computed column called PrintDate that uses the DateAdd function to add 30 days to the InvDate column.

Share ThisShare This