>Did you mean "a separate *non*-binary index"? And no, I don't have any queries that use AND NOT DELETED() etc. They were added back in the 90's when it was recommended. So given that, there is no reason I can think of for not switching them out to binary.
>

What I was trying to say is that if you need the tags for searching, you'll need both types. If they're only for optimization, then you can use just binary.


>They are supposed to be there to help with optimization of SET DELETED ON. I know I saw articles re new recommendations for that but have generally not made any changes because overall, the tables are relatively small.
>
>On one table, the deleted records are about 1% of the table (50,000 records). Would this be the best index:
>
>INDEX ON DELETED() TAG Del_Tag BINARY
>
>For another table, the deleted records are 80% of the table, would the best index be:
>
>INDE ON NOT DELETED() TAG Del_Tag BINARY
>

Just use INDEX ON DELETED() for every table. And, of course, this is irrelevant if you run with DELETED OFF; it's only relevant when DELETED is ON, because that adds an implied condition of AND NOT DELETED() to every FOR and WHERE clause.

For an explanation of this topic overall, read the section titled "Deletion and optimization" in my paper that you've already looked at for correlated subqueries.

Tamar