Microsoft® SQL Server™ 2000 Programming by Example

Indexes on Computed Columns

In SQL Server 2000, you can create computed columns in a table. These columns don't use storage space, and SQL Server maintains them automatically whenever the underlying data changes.

You can create a computed column SalePrice in the [Order Details] table to get the total sale value of every row, considering the unit price and quantity. To speed up the process of searching, or sorting, for this SalePrice column, you can create an index on this computed column, and Query Optimizer might use it, if necessary. Listing 6.33 shows the complete process.

Code Listing 6.33. It Is Possible to Create Indexes on Computed Columns

USE Northwind GO -- Create the computed column SalePrice ALTER TABLE [order details] ADD SalePrice ...