Hi Walter --

>>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.

I think the traditional view was valid before NOFILTER, and still is without NOFILTER. Of course, there is rarely use for such queries now, so the traditional view needs an update for current SQL methods...

>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.

Agreed, if SET DELE OFF, and probably Deleted is part of WHERE...

>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%.

My question is, though: if SET DELE ON, is the Deleted index EVER used in a NOFILTER query? Or is it only available as a filter in a non-NOFILTER query?

That is, no matter how many deleted recs are in a table, is there ever a benefit in the deleted index if you:

1) Always use NOFILTER
2) Never use SET DELETED OFF