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 (http://blog.SQLAuthority.com)
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,
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.
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
[...] Introduction to Force Index Query Hints – Index Hint – Part2 [...]