The Microsoft SQL Server 7.0 query processor provides index intersection. Index intersection allows the query processor to consider multiple indexes from a given table, build a hash table based on those multiple indexes, and use the hash table to reduce I/O for a query. The hash table that results from the index intersection becomes a covering index and provides the same I/O performance benefits that covering indexes do. Index intersection provides greater flexibility for database user environments in which it is difficult to determine all of the queries that will run against the database. A good strategy in this case is to define single column, nonclustered indexes on all columns that will be queried frequently, and let index intersection handle situations in which a covered index is needed; for example:
SELECT col3 FROM table1 WHERE col2 = 'value' CREATE INDEX indexname1 ON table1(col2) CREATE INDEX indexname2 ON table1(col3)
Or from SQL Server Enterprise Manager, use the Create Index Wizard.
In the preceding example, indexname1 and indexname2 are nonclustered, single column indexes created on the SQL Server table called table1. When the query executes, the query processor recognizes that index intersection using the two indexes is advantageous. The query optimizer automatically hashes the two indexes together to save I/O while executing the query. No query hints were required. Queries handled by covering indexes (whether by explicitly declared covering indexes or index intersection) are called covered queries.
For more information, see SQL Server Books Online.