SQL SERVER – Introduction to Force Index Query Hints – Index Hint – Part2

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

Example 2: Using OPTION clause

USE AdventureWorks
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)))

Reference : Pinal Dave (http://blog.SQLAuthority.com)

13 thoughts on “SQL SERVER – Introduction to Force Index Query Hints – Index Hint – Part2

  1. I am trying to do a select * with a filtered index and cannot get it to work. I want to use it similar to set the controlling index so that I can do a select * and only return the records in the index. Something like below.

    SELECT *
    FROM Person
    WITH (INDEX(AK_Contact_rowguid))

    Any clues as to how this can be achieved.


  2. Hi,

    could you please let us explain on which columns we should create a clustered/non-clustered index on a table.
    for example there is a query select col1,col2,col3, col4,col 5 from table1 where col3 > 5 and col3 < 10.
    and moreover, this query is used quite heavily for a specific dept, and i want to create a index on it, then which columns i should consider for creating index on this above table.


  3. Hi,

    I am currently working on a project that requires me to dynamically build site navigation using recursive loop. Once a user select a category from navigation or provide a search keyword, the selected category/keyword acts as a filter to re-generate the entire navigation.

    I have the navigation listing generated and with every category in the navigation I need to put a count for the available recipes that lie under that category after applying the filters, i.e. if I have selected category 1 and 2, and I have a keyword “chicken” my query should re-generate the navigation and then along with each category it should put a count that tells me how many remaining recipes each category holds that is common in the selected filters cat 1 and 2, and also has a keyword chicken.

    I am using an intersect to pick the common recipes that exists in the provided filters (selected categories) and the category in the loop. The query I have gives accurate results but the execution time is too slow due to intersect.

    SELECT Categories.CategoryID, Categories.CategoryTitle, CategorySEO.PageTitle,
    (SELECT Count(*) AS xCount
    (SELECT Count(ItemID) as xCount
    FROM Collections C WITH (INDEX(i__au_CategoryID))
    INNER JOIN Recipes R WITH (INDEX(i__au_RecipeTitle)) ON C.ItemID = R.RecipeID
    WHERE C.CategoryID = Categories.CategoryID
    AND C.ItemID IN (
    (SELECT ItemID
    FROM Collections WITH (INDEX(i__au_CategoryID))
    WHERE (CategoryID = 15)) –Category Filter 1
    (SELECT ItemID
    FROM Collections WITH (INDEX(i__au_CategoryID))
    WHERE (CategoryID = 125)) –Category Filter 2
    –GROUP BY C.ItemID
    AND R.RecipeTitle LIKE ‘%chicken%’ –Keyword Filter
    GROUP BY C.ItemID,R.Active
    HAVING R.Active=1
    ) AS derivedtbl_1
    ) AS RecipeCount
    FROM Categories
    LEFT JOIN CategorySEO ON Categories.CategoryID = CategorySEO.CategoryID
    WHERE (Categories.ParentCategory = 2) AND (Categories.Active = 1) AND (Categories.CategoryTitle’Uncategorized’)
    ORDER BY Categories.CategoryID

    As you can see I have applied indexing on columns categoryid and recipetitle. But doesn’t seems to be giving any better performance.

    Any thoughts?



  4. Hi

    Could you please help me with this:
    I have a table variable
    col2 INT NOT NULL,
    filler CHAR(200) NOT NULL DEFAULT(‘a’),
    how could I force using the unique index as it does not have a name


      • I mean you cannot apply
        WITH (INDEX(index_name_here))

        to the unique index in the table variable above

        in fact how could we force sql server use the unique index when we know it would be more efficient then using a scan in the PK index

        select * from @T where col2 = 2
        is not using the unique index


  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  6. My SQL is running very slow, Below is the SQL

    from salesfact a11
    join Customer a12
    on (((a11.Customerid + a11.PropertyID) + 1) = ((a12.PropertyID + a12.Customerid) + a12.SourceID))

    there are index on the columns used in the join. Not sure why the sql is not using the index. Any ideas will be appreciated


    • Indexes are no magical things. They’re like a dictionary: if you search for a single word you can easily find it. If you search for all words that begin with ‘WOR’ you can easily find them. But if you search for all words that contain ‘ORD’, or all words of length 13, that’s more difficult, you basically have to parse the whole dictionary. Your search is of the latter kind: you are not searching for a known CustomerID or PropertyID, you are searching for rows with certain properties that make it difficult for MS SQL to guess what their CustomerID or PropertyID look like; and therefore, MS SQL cannot use the indexes.


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