INDEX HINTS are normally not needed - the query optimizer does a great job of determining what index should be used to perform a query.
I just came across a situation where the wrong index was chosen - and I thought I would share it with the group.
Some background info - Financial system - LEDGER table.
Simplied version of columns - FISCALYR (int), TRANSENTRY (int), POENTRY (int), DEBITAMT (money), ENCUMBEREDAMT (money).
Primary key - clustered - FISCALYR+TRANSENTRY
TRANSENTRY is a unique number for a fiscal year.
Alternate key - FISCALYR+POENTRY
POENTRY is repeated for all rows related to the same PURCHASE ORDER.
The QUERY used was:
Code:
Select -Sum(Encumbamt) From Ledger_T LE
Where LE.FiscalYr = 2006
and LE.POEntry=917 and LE.TransEntry<>3314
About 6 rows exist with POENTRY of 917.
30000 or more rows exist for a fiscal year. We are looking to exclude a single row - based on it's TRANSENTRY value.
The QUERY OPTIMIZER used the PRIMARY KEY with a TRANSENTRY < 3314 or TRANSENTRY> 3314 execution plan - basically amounting to a TABLE SCAN of 30000 rows. The fact that TRANSENTRY was found in the WHERE clause caused the PRIMARY KEY to be chosen.
Bad choice - the ALTERNATE KEY on the FISCALYR+POENTRY would amount to just the 6 rows being looked at.
This is the query with an INDEX HINT - forcing the SQL engine to choose the correct INDEX
Code:
Select -Sum(Encumbamt) From Ledger_T LE
With (Index (AKLedger1))
Where LE.FiscalYr = 2006
and LE.POEntry=917 and LE.TransEntry<>3314
If you are curious how to create ALTERNATE KEY indexes - this is the T-SQL to do so...
Code:
CREATE INDEX AKLedger1 on Ledger_T (FiscalYr,POEntry)