Walter,

Thanks a lot for your input.

>Hi Naomi
>
>Even with VFP9, it is best not to use index ON DELETED(), no matter the enhanced optimization.
>Though the binary tags are much smaller, it truly does not add much to it. If there are no or very few deleted records, there simply is nothing to optimize, but only to lose. There is no way VFP could speed this up without filtered resultsets.
>
>The 10 times faster queries are probably due to filtered resultsets. To test this add a NOFILTER to the SQL statements. This would only apply to very simple queries, that would lose its advantage when actually processing the resultset.
>
>If you want to test this, you have to test this properly in order to make appropriate conclusions:
>- Run the query accross the network.
>- Make sure the accessed tables are in use by other workstations and actually have written to the tables so it disables opportunistic locking.
>
>This way you'll prevent to be fooled by (OS) caching, and all neccesary data is transferred from the server to the testing machine.
>
>
>It is also wise NOT to use any filtered index on DELETED() (e.g. INDEX ON Myfield FOR NOT DELETED()) as this would include the tag every time in each query when SET DELETED is ON. It acctually would decrease performance in most cases.
>
>
>So, in short, I would say no. Don't add it to your tables, VFP does not need it. IF you add it, you'll force VFP to use it on every query and actually drags performance down, though very simple SQL SELECTS might return filtered resultsets in a fraction of a second. Those filtered resultset might actually cause you additional headaches as they are not real cursors, but rather a filter on the original table. In most cases you want to avoid filtered resultsets at all cost.
>
>
>Walter,
>
>>Hi everybody,
>>
>>I'm wondering if anyone has some input on using binary indexes, particularly index on deleted() tag deleted binary.
>>
>>Here is our situation:
>>We don't delete records at all, but we're running with SET DELETED ON in case we do delete. Does it make sense for us to add this index in our tables?
>>
>>Thanks in advance.