Index not getting used + sql server 2005

Question

Here is my query

Declare @StartDateTime datetime, @EndDateTime datetime

Select @StartDateTime = '2010-11-15', @EndDateTime = '2010-11-16'

Select PracticeCode, AccountNo, ProcCd, Modifier , ChargeDos, Paid as Amt, CreatedDate,
     case When Paid > 0 then 'P' 
          When Paid = 0 and WrittenOff = DueAmt then 'A'
          Else 'O' 
     End as Status
     From Trn_Postings
     Where CreatedDate >= @StartDateTime and CreatedDate <= @EndDateTime
            --and ManualOverride in ('S','F','X','G','O')
            and ManualOverride in ('N','U')

Edit : Created date is a datetime column which contains the date & time of the record created

I have individual indexes on both CreatedDate and ManualOverride. But the execution plan shows clustered index scan. The table has nearly a million record and can grow 4 to 5 times in near future.

The most surprising part is if I change the where clause like below, it uses both the indexes. I just dont know why.

     Where CreatedDate >= @StartDateTime and CreatedDate <= @EndDateTime
            and ManualOverride in ('S','F','X','G','O')
            --and ManualOverride in ('N','U')

How do I make Sql to use the indexes...

Further if I use a Not in clause wont the index be used.

Answer 1

I'd suggest creating a composite index over CreatedDate and ManualOverride. When both conditions can be satisfied using indexes, that's what the optimizer appears to be doing.

But once it knows that it has to use the clustered index to satisfy part of the query, it would appear that it's ignoring other indexes. It has decided that COST(Use CreatedDate index + CI lookup + condition) > COST(Scan CI). It's wrong, but sometimes this happens. An index over both columns (CreatedDate first) would probably be used. Or you can try to force it's hand by using a INDEX query hint. It may still choose to ignore the hint.