Bruce,

>The traditional view is that the # deleted records makes no difference, the Deleted tag always improves performance independently. Of course, we have discovered that with NOFILTER the tag doesn't necessarily improve performance, and may even hinder it, but this testing is with no deleted records.

I hope to let people see that this traditional view wrong. They where probably mislead by the label of 'Full optimization' or by examples where the optimizer just adds a filter to the table. As you did state before: Fully optimization does not neccesary mean that it is faster than partial optimization.

>The question is now, is the Deleted index of value with some deleted records in the base table? If so, at what rough percentage of deleted recs will it improve performance? My first thought is that the tag may not improve performance even with many deleted records, if the traditional view above is correct. But further testing is needed on this...

If you look at this from a theorecticly point of view there is NO way that a TAG on delete could benefit performance if there are no deleted records. This is simply not possible. If i have a table persons which contains males only there is no way that an index on Sex could be of any benefit of any kind of query where you want to include males. The story would be different if you want to exclude males; then the optimizer discovers by the index that the table contains NO females and therefore doesn't have to search the table for females.

So a TAG on deleted() could be of benefit if you want to find deleted records.

Theoreticly spoken if you table contains about 50% deleted records your gain of performance could be 100% as the optimizer just can select 50% of the table by the index. This only by theory because the optimizer has to use and index to get this information, In Practice the speed gain would be something below 100%.

regards,

Walter