Full table scan occured even when index exists?

Question

We have a sql query as follows

select * from Table where date < '20091010'

However when we look at the query plan, we see

The type of query is SELECT.

    FROM TABLE
        Worktable1.
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 32 Kbytes for data pages.
    With MRU Buffer Replacement Strategy for data pages.

which seems to suggest that a full table scan is done. Why is the index not used?

Answer 1

If the majority of your dates are found by applying < '20091010' then the index may well be overlooked in favour of a table scan. What is your distribution of dates within that table? What is the cardinality? Is the index used if you only select date rather than select *?

Answer 2

Unless the index is covering *, the optimizer realizes that a table scan is probably more efficient than an index seek/scan and bookmark lookup. What's the expected selectivity of the date range? Do you have a primary key defined?