Hi Nancy,

>>Remember, too, *not* to include any index on the expression deleted(); this will also return too many records.

>I really enjoyed your very lucid discussion of optimization. Just a small quibble. If people have a filter on DELETED() on the PK index, then they should have an index on Deleted(). Which can cause the problems you point out.

Ehhh, I'm afraid I can't follow that. Why should I need an INDEX on deleted() if I filter my PKs on DELETED() ?

>All the more reason to use surrogate keys...one doesn't need the deleted() filter.

You're shifting the problem from the Primary key (surrogate) to the candidate key (meaningfull). In your case, you should filter the candidate index on !deleted().

Whether you use surrogates or not, you'll have to filter your intelligent (whether its the primary or the candidate) key for !DELETED(): If I got an article table in which I want to prevent the article "BIKE" to occur more than one time in a non-deleted record, there is no other alternative than to use the FOR !DELETED() clause (on either the Primary index or Candidate index).

Walter,