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
INDEX table query hints when the ...