>Ed,
>
>I was not aware that my candidate key, especially with its filter, made it non-Rushmore optimizable. When I changed it to a regular key, the process took only .04 secs as opposed to 4+ secs!
>
>I guess I will just manually enforce the uniqueness of that field inside my code instead of making it a candidate key, unless there's a better way to do it?
>
The alternative here is to carry two tags, a regular key without the filter, and your filtered candidate key. I don't run into the problem since anything I would use as a candidate key would not have duplicated occurances in the table; anything that would be a valid candidate key would be unique in all instances regardless of record status (no, I don't recycle keys.)
It's the filter on the tag, not the fact that it's a candidate key, that makes the tag not be used by Rushmore optimization. Primary/candidate keys are usable for optimization without a FOR clause on the index.