>>>>If you need an index on a logical field or on deleted(), make sure to add the Binary keyword when you create it. That will make the index much smaller, meaning much faster.
>>>
>>>Not faster than not having the index at all, especially when other filters have already identified the records.
>>
>>When binary came out, we measured perf with traditional, binary and no index on deleted() on sizable data sets of a real application, as I already had a test harness based on QueryPerf in place. We weighed the results of the different data sets to the largest, as runtime duration was rising more than linear.
>>
>>Binary was the best compromize, leading to smallest total time and no really bad performance in single measurements -
>>was never the worst / an outlier. IIRC we added a more than a dozen binary indices to tables where we had previously deleted a traditional index on deleted() - after running perf tests, total table count > 300.
>
>I've done a lot of removal of the deleted index and only saw performance gains - except where someone was doing an unfiltered count with set deleted on, which became - "count for not deleted()". There are always explanations of why the deleted index had a positive effect, but these reasons were not proof.
>

The app had at first a traditional deleted() on every table. It was beneficial to remove most of them, somewhere between 80 and 90%. As this was only shuffling large amounts of data without user intervention, measurements running on the same starting set was possible. When deleted() came up, the runtime was shorter when another 5% of the tables gained a binary index, after the measurable benefit of switching the already "beneficial traditional" deleted() ones to binary.

No doubt given enough money a better approach to code the whole app might have been possible - no budget. For that particular app a small percentage of deleted() indices was the best approach. If one only compared 0 deleted() index vs. all tables with deleted() indexed, 0 would have been faster, but it was slower than having deleted() indexed at ~15% of the # of tables. One data point, but measured with high precision and steps taken to give each run the same chances (cold start on freshly defragged disks running only a minimum of services and not other tasks.