SQL Server Index Hint

SQL Server query optimizer decides which index to use, when a query is requested. SQL Server optimizer is a cost based optimizer so it picks the index which has low cost based on performance. When we design an index for a query, optimizer will utilize the defined index to generate the plan for requested query if the index has low cost based on performance of query. But for some special scenarios or very rare scenarios where SQL Server will not use the most appropriate index for a given query and impact query performance.

If SQL Server optimizer is not using the expected index and you wanted your query to use that specific index, then it can be done using Index hint. Be aware most of the time Query Optimizer chooses the right index out of available indexes.

I usually use Index hint while designing index to compare between indexes to know which one is best for my query.

Demo 1: Use Index hint to force SQL server optimizer to use a specific Index

USE [AdventureWorks]
GO
SELECT ProductID, ReviewerName, [Comments]
FROM [Production].[ProductReview] WITH (INDEX = IX_ProductReview_ProductID_Name)

Demo 2: Use Index hint to force SQL server optimizer to use a specific Index

USE [AdventureWorks]
GO
SELECT PR.ProductID, PR.ReviewerName, PR.Comments, PP.Name
FROM [Production].[ProductReview] PR
WITH (INDEX = IX_ProductReview_ProductID_Name)
INNER JOIN [Production].[Product] PP
WITH (INDEX = [AK_Product_Name]) ON PR.ProductID = PP.ProductID

NOTE: Hints can prevent the query optimizer from choosing a better execution plan