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)