You can specify an index for each table in the FROM clause of a SELECT query.

Syntax

Use the following syntax to specify an index hint:

SELECT column_list FROM table_name [[ AS ]table_alias][ WITH (INDEX (index_name))]...WHERE ...

index_name is a string that indicates the name of the index.

If a candidate plan is generated with the specified index, the optimizer will use it. If the optimizer is unable to generate a candidate plan with the specified index, it ignores the hint.

Example

In this example, to query data, the query optimizer will use (CountryPost) as the index to execute the query if a candidate plan is generated with the specified index.

SELECT *
FROM pub.Customer WITH (INDEX (CountryPost))
WHERE Country = 'USA';

Force index hints

Using force index hints enables the SQL engine to either select the index specified in hints or select table scan to read records from the table. No other index is selected when the force index hint is specified.

Syntax

Use the following syntax to specify force index hints:

SELECT column_list FROM table_name [[ AS ]table_alias][ FORCE (INDEX (index_name))]...WHERE ...
Note:

You can also use PRIMARY in place of index_name in the above syntax. Using PRIMARY while specifying force index hints enables the SQL engine to select an index defined for the primary key or to select table scan.

Force index hints can be specified for each table in the SELECT query. When a force index hint is specified, the SQL engine checks whether or not the index hint can be used to evaluate restrict conditions, perform JOIN operations, or sort records. If the index hint cannot be used, the SQL engine performs a table scan.

Example

In this example, to query data, the user instructs the query optimizer to use (CountryPost) as the index to execute the query. 

SELECT *
FROM pub.Customer FORCE (INDEX (CountryPost))
WHERE Country = 'USA';