Christian,
only in extreme cases can an index on a Boolean column be useful - e.g. if
99% of them had IsDeleted = 'Y' and you never were intereted in these. And
even then, you would add the PK at the end of the index to avoid slow
updating (it takes time to find the correct item to update when indexes
have low selectivity).

I don't know when it is cheaper to use an index than to use natural order,
but it certainly isn't on fields with only two possible values (and then
you should not think of only one field isolated from other fields and the
queries it is going to be used with - e.g. the obvious example that an
index on the field 'country' will make selects slower when querying on the
indexed field 'town' like SELECT Name FROM CUSTOMER WHERE COUNTRY =
'GERMANY' AND TOWN = 'FULDA'). Though it is not only the case when the
fields are naturally related like town/country, but all cases where one
index is highly selective whereas the other has low selectivity.

Set

At 14:21 26.02.2003 +0100, you wrote:

>Hi,
>
>I have got a small table with about 5.000 records (large
>rows) and approx. three inserts a day. Sometimes records
>from this table have to be deleted and my customer wants
>that these are not physically deleted, but only marked
>as 'deleted'.
>
>Of course this means that the where clause of many queries
>have to be modified:
>SELECT x FROM Y WHERE IsDeleted = 'N'.
>
>I am wondering what is more expensive for the engine: to
>scan the whole table or work with an index on the IsDeleted
>column.
>
>As I said, the table is quite small, but there are lots of
>selects on it (users, SPs, report generators).
>
>I am sure some of you have already made some experiences with
>such a scenario and I would be glad if you could share them
>with me ;-)
>
>
>TIA,
>
>Christian