Does Index Intersection work with Included Columns?

Question

I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report. The index would be on a single column, CreatedDate, but would have included columns.

There are already five indexes on the table and I want to avoid creating separate indexes for specific reports or queries. However, I figure a single-column index might be useful in index intersection, particularly on a column that is likely to be used in multiple queries, like CreatedDate.

What I'd like to know, to help me make my decision, is whether an index which has included columns could be used in index intersection, or whether having included columns would prevent the index from being used in index intersection.

I've tried to Google this question and haven't found any information about the effect of included columns on index intersection.

Answer 1

Included columns don't prevent the index being used for any purpose.

The query optimizer will pick the best index (or possibly a table scan) that is the most efficient for the query being compiled. Keep indexes as small as possible, while still providing the desired functionality. According to Books Online INCLUDEd columns don't affect the query optimizer's selection of an index based strictly on size, however if any given index has all columns required for a query, either as key columns or included columns, that index will be given priority.

Since the query optimizer is a fairly opaque beast, you most likely need to test the suggested index to see if it is used, and what the resulting impact is for INSERTs, UPDATEs, and DELETEs.