This article, I will start with disclaimer instead of having it at the end of article.
“SQL Server query optimizer selects the best execution plan for a query, it is recommended to use query hints by experienced developers and database administrators in case of special circumstances.”
When any query is ran SQL Server Engine determines which index has to be used. SQL Server makes uses Index which has lowest cost based on performance. Index which is the best for performance is automatically used. There are some instances when Database Developer is best judge of the index used. DBA can direct SQL Server which index to be used to execute query.
Example 1 : SQL Server using default index
USE AdventureWorks
GO
SELECT *
FROM Person.Contact
GO
Example 2: SQL Server using forced index
USE AdventureWorks
GO
SELECT ContactID
FROM Person.Contact WITH (INDEX(AK_Contact_rowguid))
GO
Example 3: SQL Server using different index for different/same tables
USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
