In my previous article SQL SERVER – Introduction to Force Index Query Hints – Index Hint I have discussed regarding how we can use Index Hints with any query. I just received email from one of my regular reader that are there any another methods for the same as it will be difficult to read the syntax of join.Yes, there is alternate way to do the same using OPTION clause however, as OPTION clause is specified at the end of the query we have to specify which table the index hint is put on.
Example 1: Using Inline Query Hint
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
Example 2: Using OPTION clause
USE AdventureWorks GO SELECT c.ContactID FROM Person.Contact c INNER JOIN Person.Contact pc ON c.ContactID = pc.ContactID OPTION (TABLE HINT(c, INDEX (AK_Contact_rowguid)), TABLE HINT(pc, INDEX (PK_Contact_ContactID))) GO
Reference : Pinal Dave (https://blog.sqlauthority.com)