>>>If one has a primary index on a key field that sets a flag on !DELETED(), does one need a second index on the same expression w/o the filter for full Rushmore optimization?
>>>
>>
>>If you're using a FOR clause, definitely yes; I'm not certain about the situation where you have a character field and you do something to concatenate the result of an IIF(DELETED(),
some char expn,
some other char expn) as a part of the key.
>>
>>
>>>TIA
>
>Ed,
>
>You could use something like INDEX ON MyKey+IIF(DELETED(),'D',' '). This would work as long as your queries looked like WHERE MyKey+IIF(DELETED(),'D', ' ')=x. The left side has to be
exactly like the index key. That even means that in this example using double quotes in your query won't work. Also, if ANSI is set on, you'd have to add a blank space to get it to work. Might be easier to have one index :).
It's not a problem I've ever had - I use a surrogate key as a primary IAC, and I don't recycle keys, so there's no need to filter the primary key for deletion status.