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 ...

Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.