>>>>>>>
>>>>>>>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
>
>Tamar,
>I read and study this whole interesting thread and, maybe it is a silly question, but how do you create a binary index on deleted()?
>Would you please give me an example?
>Thanks,

INDEX ON DELETED() TAG IsDeleted BINARY

Tamar