Microsoft® SQL Server™ 2000 DBA Survival Guide, Second Edition

Overriding the Optimizer

Use the following features to override the optimizer:

• Index hints

• The SET FORCEPLAN ON command

Index Hints

By using an index hint, you can force the optimizer to use an index or force it not to choose an index. You usually want to let the optimizer determine how to process the query. However, it might be beneficial to override the optimizer if you find that it is not taking advantage of useful indexes. The syntax used to override the optimizer is as follows:

SELECT ... 
FROM [table_name] (optimizer_hint)
						

In this syntax, optimizer_hint has the following format:

(INDEX(index_name|index_id})) 

In this format, index_name is any valid name of an existing index on the table and index_id is the corresponding index ID.

The following ...