Why does Sql Server prefer a clustered index scan to NonClustered + KeyLookup?


So here's the query and the execution plan. The commented create-index statement has already been executed.

Why does Sql-Server decide to perform a clustered index scan? Am I missing something?

If the query would use the index, it would have to search through the index pages (at least 2), get the clustered index key from the result(s), then search with each of these keys through the clustered index (at least 2 page reads per found record) to get the rest of the record not covered by the index.

This can be an effective approach, if you have a large number of records and your where clause selects only a relative small subset of them (which the query optimizer estimates based on the index statistics).

From the looks of it you only have a small number of records in the table, maybe they even fit on one page, so the optimizers says "I can do the whole query by reading and filtering the one or two pages from the clustered index once, which is way more effective than the whole nonclustered index business in this case, so I'll do that and spare my poor overloaded server engine a whole load of trouble" :-)

Edit: Try to specify WITH (INDEX(idx_WP_Discrepancy_FilterColumns2)) before the WHERE clause and compare the estimated subtree costs in the query plan to the original query to see the difference.