Specifying an index for a query

You can specify the index to use for a query using the (index index_name) clause in select, update, and delete statements. You can also force a query to perform a table scan by specifying the table name. The syntax is:

select select_list
    from table_name [correlation_name]
       (index {index_name | table_name } )
       [, table_name ...]
    where ...

delete table_name 
    from table_name [correlation_name]
    (index {index_name | table_name }) ... 

update table_name set col_name = value
    from table_name [correlation_name]
    (index {index_name | table_name})...

For example:

select pub_name, title
    from publishers p, titles t (index date_type)
    where p.pub_id = t.pub_id
    and type = "business"
    and pubdate > "1/1/93"

Specifying an index in a query can be helpful when you suspect that the optimizer is choosing a suboptimal query plan. When you use this option:

Use this option only after testing to be certain that the query performs better with the specified index option. Once you include an index specification in a query, you should check regularly to be sure that the resulting plan is still better than other choices made by the optimizer.

NoteIf a nonclustered index has the same name as the table, specifying a table name causes the nonclustered index to be used. You can force a table scan using select select_list from tablename (0).