Microsoft® SQL Server™ 2000 Unleashed, Second Edition

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 ...