No, you can't create an index that uses functions or expressions with SQL Server 7.0 or earlier. However, SQL Server 2000 lets you directly index computed columns in a table and place indexes on views. Carefully read the SET Options That Affect Results section of SQL Server 2000 Books Online (BOL) before you set up advanced types of indexes. Several restrictions for indexes exist. For example, an index on a computed column or view works only if all operations that reference the index use the same algorithms to determine the key values. This limitation means that any SET options that affect the results that T-SQL statements generate must have the same settings for all operations that reference the index.
Seven SET options affect the results in the computed columns that views return. For all connections that use indexes on computed columns or indexed views, you must set six of those options to ON: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER. You must set the NUMERIC_ROUNDABORT option to OFF.
SQL Server 2000 BOL gives the following example to illustrate the purpose for these restrictions:
CREATE TABLE Parts
(PartIDint PRIMARY
KEY,
PartNamechar(10),
PartMaterialchar(10),
PartColorchar(10),
PartDescriptionAS
PartMaterial + PartColor
)
GO
INSERT INTO Parts VALUES (1, 'Table', 'Wood', 'Red')
INSERT INTO Parts VALUES (2, 'Chair', 'Fabric', 'Blue')
INSERT INTO Parts VALUES (3, 'Bolt', 'Steel', NULL)
GO
The value of the PartDescription column for the row in which PartID is 3 depends on the CONCAT_NULL_YIELDS_NULL option. If you set CONCAT_NULL_YIELDS_NULL to ON, the calculated value is NULL. If you set CONCAT_NULL_YIELDS_NULL to OFF, then the calculated value is the string Steel. To properly maintain an index on the Part-Description column, all INSERT, UPDATE, and DELETE operations must have the setting CONCAT_NULL_YIELDS_NULL ON as the connection that created the index. The optimizer doesn't use the index for any connection with a CONCAT_NULL_YIELDS_NULL setting that is different from the connections that created the key values.
PartDescription doesn't really exist. The value materializes as a real value only when an index key stores it. But, the value in the index key changes if you change some of the SET settings (e.g., CONCAT_NULL_YIELDS_NULL). SQL Server 2000 requires these extreme measures to ensure that connections can properly use indexes on created columns and views.