Hi Ed.

I'm sure you're right that primary and candidate tags cannot be used for Rushmore Optimization when filtered. I think you meant ALL tags. However, Craig Bernston's website under his Knowledge Base article KB00004 states....

"Primary and Candidate keys created using the FOR clause are Rushmore optimizable. This is because primary and candidate keys are handled differently than regular indexes. However, creating filtered primary and candidate keys violates their definition, which states that the key should be unique for each record."

I've made a simple test that seems to support our position. I wrote Craig long ago that I had proven this position and offered to discreetly help him to change his KB.

My simple example is this...

I have a lookup table ...

Lup_PK C(5), ;
Lup_LTpFK C(5), ;
Lup_Code C(6), ;
Lup_Description C(40), ;
Lup_Notes M)

with this index...

index on Lup_LTpFK + upper(Lup_Code) tag Lup_TCodCK candidate for !deleted()

I don't have a deleted tag (at the time of this writing). After Chris Probst's article, I use a deleted tag and set deleted on during development so I can determine Rushmore optimization based on the other keys. I rebuild my indexes without the deleted tag for testing and deployment over a LAN. In this case, I set deleted off. I also sys(3054,1). The following SQL ...

select * from lookups where lup_ltpfk+upper(lup_code) = "00001ADMIN" into cursor test

indicates that Rushmore Optimization level is NONE.

I hope Craig doesn't take offence to this, as none is intended. I'm only trying to determine the truth for all our sakes.