Using FORCESEEK and INDEX table hint

A major role of a query optimizer is to choose the best execution plan among the different available plans for query execution. In most of the cases, query optimizer always chooses the right execution plan, and we generally don't need to specify query hints to force the query optimizer to execute a query in a desired way. However, in rare cases, it can happen that query optimizer may fail to choose the right query plan.

SQL Server allows us to specify query hints while writing queries, which forces the query optimizer to execute the query in a specific way only.

In this recipe, we will see how we can force a query to perform an index seek operation by using the FORCESEEK and INDEX table query hints when the query optimizer performs an index scan operation.

Getting ready

In this recipe, we first see that certain ProductIDs cause an index scan operation while retrieving data from the Sales.SalesOrderDetail table. Then we will use the query hints INDEX and...