Adam wrote:

>
> This is possibly OT for a reply to this post, but I am interested to
> know what sort of percentage of elimination of records would you say
> the cost of using the index outweighs its benefit? Obviously 1-2% is a
> given that the index will not help, but what about 20%? Asides from
> caching, what other factors influence this?

The major cost is reading a data page, so the benefit is pages not
read. And that is really hard to describe in the general case.
If the data is effectively clustered on the key field, then the
index helps. If it is distributed so every page has at least one
record that contains the value, then the index doesn't help much.
It avoids reading some records on a page, but that's a CPU cost
and much smaller than the cost of reading a data page. Then
there's the cache question - if the page is already there...

Not an easy question.


Regards,


Ann