>>>>>>>>
>>>>>>>>No need to be rude. If you MUST have a useless index, of course, make sure it's as small as possible. That's your argument.
>>>>>>>>
>>>>>>>>I know the precisely why the feature exists. It was built to improve a situation that many people believe, which is mostly a myth. If you have one deleted record in 5 million records and your queries are regularly producing small sets of records, only 1 in 5 million times and possibly never, will that deleted index help at all.
>>>>>>>
>>>>>>>Please reread my replies. If you find any of them rude, I promise that it wasn't intentional. My ONLY point is that you must compare with identical conditions. If you compare a steam engine with a turbo charged engine running on rocket fuel, of course you will get different results. A binary index is only one eighth in size compared to a normal index, which you don't take into consideration. That's why you compare apples to oranges.
>>>>>>>
>>>>>>>Also know that I never delete records, so I never use index tags on deleted(). However, that's not relevant in this argument. 'nuff said.
>>>>>>
>>>>>>I disagree. The comparison must be whether you use an index on deleted or not - not whether it is binary or not. If the index is used on a local machine versus over a LAN there is a performance difference again. If you construct your system properly, it is not needed.
>>>>>
>>>>>And this is why you are totally wrong. Using your analogy, running trains is a bad idea since they use coal and pollute the air. And all airplanes are slow since they use combustion engines and propellers.
>>>>
>>>>First off, the fact the the OP doesn't know these basis things is a real shame. Secondly far too many believe having a deleted index is needed. It isn't most of the time. Doing something unnecessary but faster is still a waste.
>>>>
>>>>http://www.tomorrowssolutionsllc.com/Articles/Speed%20Up%20Your%20SQL%20Code.pdf
>>>>
>>>>"As a result, for a large table and a small result set, you're usually better off without an index based on the DELETED() function"
>>>>
>>>>"Clearly, reading the binary index is less likely to slow a command down" - Less likely becomes impossible if you don't use them at all.
>>>
>>>Right, but there's a trade-off between reading an index and reading data. To figure out which records are deleted and omit them, you have to do one or the other. The question is which costs more, and the answer is "it depends." Binary indexes changed the equation by lowering the cost of reading the index. It still depends, but the calculation is different.
>>>
>>>Tamar
>>
>>Binary indexes are intended for use with Male/Female and other binary data sets. Most Fox people I've seen still swear that deleted indexes are absolutely essential. and cannot be reasoned with. The deleted tag is of no value in the normal kinds of queries.
>>
>>I have a million record table. I deleted the first 250,000 records.
>>
>>SET TALK OFF
>>SET ECHO OFF
>>SET STATUS bar OFF
>>
>>*250,000 deleted records at the top of the table.
>>USE SPEED EXCL
>>IF TAGNO("XDELFLAG")#0
>> DELETE TAG XDELFLAG
>>ENDIF
>>
>>SET DELETED on
>>a=SECONDS()
>>FOR x = 1 TO 10000
>> SELECT id FROM speed WHERE ID=1000000 INTO CURSOR test nofilter
>>ENDFOR X
>>?"Set deleted on, no deleted tag",SECONDS()-M.A
>>
>>IF TAGNO("XDELFLAG")=0
>> INDEX on deleted() TAG xdelflag
>>ENDIF
>>
>>a=SECONDS()
>>FOR x = 1 TO 10000
>> SELECT id FROM speed WHERE ID=1000000 INTO CURSOR test nofilter
>>ENDFOR X
>>?"Set deleted on with deleted tag",SECONDS()-M.A
>>
>>USE IN SELECT("speed")
>>
>>1.906 without the deleted tag.
>>1.923 with the deleted tag.
>>
>>So the deleted tag made the process slower - it certainly did not make the magical improvement too many blindly believe. This was on a local machine and not over a LAN. I used to follow the deleted index crowd, but that was before the article about non-discriminating indexes.
>
>The LAN makes a difference, as does having the deleted records scattered through the file rather than all up front.
>
>I agree that it's no longer as simple as "index on deleted()." That said, I've seen the difference that index can make. However, if you think about what's going on behind the scenes, it's clear that what you're dealing with is a trade-off. When there's an index, the appropriate portion of the index has be read. When there's no index, the actual records have to be read. The question is which one causes more traffic.
>
>I haven't done the testing, but it seems to me the binary tag on deleted() is likely to be a good trade-off for tables with lots of deleted records scattered throughout where you usually need lots of fields at once (that is, where the cost of reading a given record is large). More often than not, you're likely to be better off without that tag, but that's not the same as saying it's never useful.

I'd say nobody does the testing, especially if you haven't. The record is being read to check other conditions. I go with the never useful argument and try to find situations where it becomes a necessity. One example: If the design must include the # of available records and set deleted is on, Fox will use the deleted index tag branch header to get the count. Personally - I ONLY show the count of the records in the cursor at the moment. That means I've already filtered out deleted records. The conditions where the deleted tag is essential are very rare, when things are done right.