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)
16 Comments. Leave new
nice article, never knew it possible to do…
thanks
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.
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
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
What did you mean by “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
We cannot create index on table variables. But it is possible on temporary tables
However you can create primary key with clustered index at the time of declaring the table variable
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.
Thanks for the comment @Fred
Hi Pinal,
Above query is not working for me..any help is appreciated.
What’s the error?
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 ?
Sometimes SQL has bugs and it can’t choose right indexes/join and hence the hints.