walter,

>Even if it has significant amounts of deleted records (say 10%) the netto result of having a DELETED() tag is insignificant. When going over a 50% it is getting benificial.

You seem to fail to understand the relative time differences between memory access time, local disk access time and network file access time. There are orders of magnitude of time difference between these three items.

In a very rough test I could retrieve 6 megabytes per second across a 100 Mb network. I could retrieve 26 megabytes per second off my local hard drive and 130 megabytes per second from memory.

The fact that a binary index can be brought across the network wire about 18 times faster is quite significant because that is the slowest operation being performed. The time to construct a bitmap and then AND it into the current result is quite a bit faster compared to the retrieval time of the actual data rows across the wire that could have been eliminated via the Rushmore bitmap. The current microprocessor instruction sets have single commands that will AND two blocks of memory together so they fundamentally work at memory access speed.

Ask yourself the question why would Microsoft even bother adding this binary functionality to the CDX if there were no benefits to it?

>You avoid the real issue here. If you want to take part of a constructive discussion then please leave this kind of bogus arguments at home.

You are the one that keeps introducing "no deleted" records into the discussion not me. I was merely pointing out if that truly is the case SET DELETED OFF is an available option. You are quite free to treat that as a specious argument if that's how you want to treat it.

>Again you don't seem to understand that with very few or no deleted record there is from a logical point of view NO WAY using an index on deleted() is speeding up the query. The index does simply not weed out any records, so processing the index tag was useless and a waste of resources and time. And even if there are one or two records in the optimizable portion of the query, filtering out those without an index, is not going to be slower than downloading a whole index tag and processing it in a rushmore bitmap.

It amounts to a tradeoff between how long it takes to retrieve the index, construct the bitmap and AND it, compared to retrieval time of a record that won't actually make it into the result set. This also depends a lot on the size of the row and the actual network performance. So I'll stand by my assertion that it should be left up to individual testing to determine if it helps performance. I'm not one to dismiss it out of hand because of a "feeling" that it won't help.

>Again, how many are doing this?? And how can you do this while developping an application that is not in production yet ??

I for one do quite a bit of development and production testing in a true production environment. I'll just say that good developers have a decent handle on performance testing.