Committed with SQL

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.

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

USE [AdventureWorks]


SELECT ProductID, ReviewerName, [Comments]

FROM [Production].[ProductReview] WITH (INDEX = IX_ProductReview_ProductID_Name)




Demo2: Use multiple Index hint for multiple tables

USE [AdventureWorks]


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


If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

About these ads

January 1, 2014 - Posted by | Index | , , ,


  1. nice one Sir ,

    Comment by Avinash Reddy Munnangi | January 2, 2014 | Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: