Indexes on Computed Columns

SQL Server 2000 allows you to build indexes on computed columns in your tables. Computed columns can participate at any position of an index along with your other table columns, including in a PRIMARY KEY or UNIQUE constraint. To create an index on computed columns, the following SET statements must be set as shown:

 SET ARITHABORT  ON  SET CONCAT_NULL_YIELDS_NULL  ON  SET QUOTED_IDENTIFIER  ON  SET ANSI_NULLS  ON  SET ANSI_PADDING  ON  SET ANSI_WARNINGS  ON  SET NUMERIC_ROUNDABORT  OFF

If any of these seven SET options were not in effect when you created the table, you get the following message when you try to create an index on the computed column:

 Server: Msg 1934, Level 16, State 1, Line 2  CREATE INDEX failed because the following SET options  have incorrect settings: '<OPTION NAME>'.

Additionally, the functions in the computed column must be deterministic. A deterministic function is one that returns the same result every time it is called with the same set of input parameters (for information on which functions in SQL Server are deterministic, see Chapter 26, "Using Transact-SQL in SQL Server 2000").

When you create a clustered index on a computed column, it is no longer a virtual column in the table. The computed value for the column is stored in the data rows of the table. If you create a nonclustered index on a computed column, the computed value is stored in the nonclustered index rows but not in the data rows unless you also have a clustered index on the computed column.

Be aware of the overhead involved with indexes on computed columns. Updates to the columns that the computed columns are based on result in updates to the index on the computed column as well.

Indexes on computed columns can be useful when you need an index on large character fields. As discussed earlier, the smaller an index, the more efficient it is. You could create a computed column on the large character field using the CHECKSUM() function. CHECKSUM() generates a 4-byte integer that is relatively unique for character strings but not absolutely unique (different character strings can generate the same checksum, so when searching against the checksum, you need to include the character string as an additional search argument to ensure that you are matching the right row). The benefit is that you can create an index on the 4-byte integer generated by the CHECKSUM() that can be used to search against the character string, instead of having to create an index on the large character column itself. Listing 34.3 shows an example of applying this solution.

Listing 34.3 Using an Index on a Computed Checksum Column

 -- First add the computed column to the table alter table titles add title_checksum as CHECKSUM(title) go -- Next, create an index on the computed column create index NC_titles_titlechecksum on titles(title_checksum) go -- In your queries, include both the checksum column and the title column in --   your search argument select title_id, ytd_sales    from titles    where title_checksum = checksum('Fifty Years in Buckingham Palace Kitchens')      and title = 'Fifty Years in Buckingham Palace Kitchens'