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

, ,
Previous Post
SQL SERVER – Introduction to Force Index Query Hints – Index Hint
Next Post
SQL SERVER – 2008 – Download Microsoft SQL Server 2008 Express with Tools Free

Related Posts

16 Comments. Leave new

  • nice article, never knew it possible to do…
    thanks

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Nabil H Usmani
    November 5, 2010 1:02 am

    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
    FROM
    (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
    INTERSECT
    (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?

    Nabil

    Reply
  • Hi

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

    Reply
    • What did you mean by “it does not have a name”?

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

  • We cannot create index on table variables. But it is possible on temporary tables

    Reply
    • However you can create primary key with clustered index at the time of declaring the table variable

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

    Reply
    • 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.

      Reply
  • Hi Pinal,
    Above query is not working for me..any help is appreciated.

    Reply
  • Hello there, my main concern would be: why we need to add that hint ?:

    example, I have a keylook up in my plan, it is removed once I use that hint, but …. should not happen automatically
    with no need of the hint ?

    Reply

Leave a Reply

Menu