SQL Server Table Hints

Index hints (a form of a table hint) are used to specify which index or indexes you want used when a query runs. When you specify one or more indexes, the Query Optimizer will use them when creating the execution plan for your query.

The syntax for a table hint is:

SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...])

Where:

column_list is the list of columns you want SELECTed.

table_name is the name of the table with the relevant indexes.

index_name is the index name or index ID for the index or indexes you want used by the Query Optimizer.

Here's an example:

SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index))

While you can use an index ID instead of the index name, this is not a good idea. This is because index IDs can change if you drop and recreate the indexes differently than when you first created them.

If you want to specify multiple indexes, to force an index intersection or an index covering as a result of an index join, you can by adding each index name or ID, one after another, with each one separated by a comma.

If you want to force a table scan, use the hint: (INDEX(0)).

Index hints should only be used when the Query Optimizer, for whatever reason, does not select the available index, and if using the index provides a performance enhancement over not using an index.

The FASTFIRSTROW table hint is functionally equivalent to the OPTION FAST (number_of_rows) hint, and either one can be used interchangedly, although Microsoft recommends that you use OPTION FAST, as support of FASTFIRSTROW may be removed in future versions of SQL Server.

For queries that return large resultsets, this hint can be handy. What this hint does is to tell the Query Optimizer to retrieve X number of rows of the total resultset as fast as it can, then continue on and return the full resultset as time permits. This acts to get the initial part of your data retrieved very quickly.

On the other hand, while it generally speeds up the retrieval of the first X number of rows, the rest of the rows are often returned more slowly that they would have been if the hint had not been specified.