Hints - Inside the SQL Server Query Optimizer - page 224

INDEX and FORCESEEK hints
The INDEX and FORCESEEK hints are table hints, and we'll consider each in turn.
The INDEX hint can be used to request the Query Optimizer to use a specific index
or indexes. Either the index id or the name of the index can be used as a target for the
Query Optimizer, but a name is the recommended way, as we do not have control of
the index id values for non-clustered indexes. However, if you still want to use index id
values, or you are interested in them for some other reason, they can be found on the
index_id column on sys.indexes ; index id 0 is a heap, index id 1 is a clustered index,
and a value greater than 1 is a non-clustered index. On a query using a heap, using the
INDEX(0) hint results in a Table Scan operator being used, and INDEX(1) returns an
error message. However, a query with a clustered index can use both values: INDEX(0)
will force a Clustered Index Scan, and INDEX(1) can use either a Clustered Index Scan or
a Clustered Index Seek. On the other hand, the FORCESEEK hint can be used to force the
Query Optimizer to use an Index Seek operation, and can work on both clustered
or non-clustered indexes. It can also work in combination with the INDEX hint, as we'll
see later.
In addition to helping to improve the performance of your queries, in some cases you
may also want to consider using an index hint to minimize lock contention or deadlocks.
Notice that, when you use an INDEX hint, your query becomes dependent on the exist-
ence of the specified index, and it will not compile (or will stop working) if that index
is removed. Using FORCESEEK without an available index will also result in an error, as
shown later in this section.
You can also use the INDEX hint to avoid a bookmark lookup operation, as in the example
shown in Listing 7-18. Since the Query Optimizer estimates that only a few records will
be returned by the next query, it decides to use an Index Seek - Key Lookup combination,
as shown on Figure 7-13.