>Yes, it's complex issue. My feeling is that if resulting recordset is large than record-by-record check for deleted() flag (if there is no index) would be slow; and if recordset is small (i guess here the difference is) then I use SEEK/INSERT. Also, it's all about one-table query, and many situations are for multi-tables.
>I have one more 'generic' consideration. I believe, you're familiar with pretty common practice- never delete records (interactively at least). Instead of this, each record may carry some Character,1 field for fancy flag, e.g. 'D'-deleted, 'N'-new, 'C'-changed and so on. Personally, I never used this way, but many people around do. So, let say we have this flag and the next question is: would it be reasonable to have index tag in this field? It seems as quite reasonable. So, if it's reasonable to have tag on some makeshift flag, then the same can be applied to deleted(), which is actually the same flag, only ready-to-use.
I actually use something like this in a situaution, but it has further use and more code values as a "bookkeeping record" for transactions...but I don't think your theory is quite right here (although you say "seems quite reasonable," which leaves some wiggle room :)
According to the way indexes are pulled into memory in non-filtered SQL results, it still doesn't make sense to have such an index, if more than 50% (roughly) of the records will have one value and that value is used in the WHERE clause...
The Anonymous Bureaucrat,
and frankly, quite content not to be
a member of either major US political party.