John,

>This statement is not correct, at least when Set Deleted is ON. In the classes I teach, I have a 1.5 million record table to illustrate the effects of having a tag and not having a tag on deleted records. The difference in performance is quite impressive.

I forgot to mention that this only counts when you don't have considerable amounts of deleted records. If you don't, You won't have any benefit from the deleted tag. Make sure that you don't deal with filtered resultsets.

>Set Deleted On is an implicit filter on deleted records. As with any filter, for best performance, especially when dealing with a large data set, you should have an index on that filter expression.

You're quite right, But if the filter is on a column (or expression) with a very low selectivity (It returns very high percentage of the actual sourcetables) an index is not going to speed up performance very much. On the other hand, it could degrade performance when replacing, appending, reindexing data and the index could get corrupt. Then the index is more of burden than it helps.

>In terms of being a burden, I don't quite understand that. Indexes always have to be maintained anyway. One additional index is really not that big of a deal.

In situations where you want to high speed appending data (for eg: An indexed SQL view) you'll find performance suffers a lot from the index. The statement I was trying to make (I agree i failed, but did refer to the discussion held a few months back) is that you should pick out your indexes carefully. If you don't have many deleted records in your tables an index is not going to help you speed up command unless:

- You want to use COUNT to count records for an fully optimized for clause(VFP then only reads the index nodes).
- You want to make use of filtered resultsets made by SQL-SELECT (Of course then you can use the SET FILTER command as an alternative for the SQL-SELECT).

Some will say: Hey why not adding the index, if it doesn't hurt ?
Well, In my experience it often hurts, when you'll get accidental filtered resultset which can lead to difficult trackable errors (like described earlier in this thread).

Besides this, I have a general rule: Only add indexes when you'll really need them. If a table contains only male persons you won't need an index on the gender column (unless you regular want know if there are female persons). IOW the selectivity of the index must be high.

Walter,