Index on a bit column

Question

Consider a table with a bit column indicating whether the object is active or inactive where the majority of the items are inactive (closed).

My understanding was that because of the limited number of distinct values for this column (2), the SQL Engine found it more efficient to perform a table scan to find the open items rather than attempt to index over a bit column.

SQL 2008 has a new feature that allows filters on an index. Without know much about the internals of it, I would assume that the index contains a reference to a record only if it meets the filter criteria and that this approach would provide an efficient means of retrieving all of the active records without having to resort to splitting the active records into separate tables or partitions.

I used to place the primary key of the open records records into a table that I used to identify the active records and then joining the main table to this "active list" table to return only the active records.

Is there any reason why using a filtered index for this purpose would not be appropriate in this situation?

Answer 1

A filtered index on an Active bit field is a valid choice.

You will probably want to add a specific UPDATE STATISTICS for filtered indexes (especially on volatile data) that specifically updates their stats with a FULLSCAN. The good news is that they are likely to be smaller indexes (and therefore easier/less-costly statistics to update)

This is because the update statistics threshold is based on the underlying column rather than the filtered index values only.

Ref.