A simple question was posed today while giving a presentation on query plan analysis to colleagues at the office.

What’s the difference between a composite (multi column) index and an INCLUDE covering index?

At the time I was stumped.  Fortunately I don’t have any problem admitting I don’t know something… it happens all the time.

Ultimately, the answer to the question is at http://msdn.microsoft.com/en-us/library/ms190806.aspx

If you don’t feel like ferreting out the answer from  the post I’ll summarize:

1. The composite index (where all of the columns are “key” columns) will carry the data in all levels of the index; the INCLUDE index will only carry the “non key” columns in the leaf node.  Take away: The INCLUDE index will be smaller than the equivalent composite index.

2. The INCLUDE columns(non-key) will not count towards the max index size (900 bytes).  Weak argument since we really should not have indexes that big anyway…

3. Updates to non-key columns in INCLUDE indexes will not contribute to index fragmentation; updates to unused “key” columns in the composite index will result in fragmentation…  IMO, this is a biggie.

4. Assuming low index fragmentation, both composite and INCLUDE indexes will perform comparably.  All columns in the composite index need not be in the query predicate.

Thanks for the lesson… and upping my IC (intellectual curiosity).

Dave