Wednesday 17 March 2010

Force SQL to use a named index....

Should SQL chose a less than optimal plan, you can influence index choice.I say influence, i mean FORCE :)

A SQL Query (I know it uses * !)
SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2 ON Table1.Id = Table2.Id
Add an index hint e.g.
SELECT Table1.*, Table2.*
FROM Table1 WITH(INDEX(IndexName))
LEFT JOIN Table2 ON Table1.Id = Table2.Id
You can still add NOLOCK to that hint too...
SELECT Table1.*, Table2.*
FROM Table1 WITH(INDEX(IndexName),NOLOCK)
LEFT JOIN Table2 ON Table1.Id = Table2.Id
A hint could equally have been applied to the joined table or both.

Links :
Brent Ozar has some good reasons for NOT using index hints though so it's only for use as a last resort.
Andrew Chen's post on Index Hints
You can also force SQL's choice of Query Plan

No comments: