Mike Dougherty-384281 (7/13/2011)
...In the case of rarely-write, frequently read data with groups that are sometimes requested via either 2nd or 3rd key specificity in the where clause, I would expect the multi-key covering index to perform better than included columns
It depends on how frequently "sometimes requested" is, and how big a datatype that extra column is. Since an index is a tree structure, adding that extra column as part of the index key means SQL will be able to store less record keys on all the index pages, so while the sometimes query with the extra key will perform better, the queries that only use the first key fields would have to do more I/O to scan the same number of record keys. With INCLUDE columns, that extra value is only stored in the leaf nodes of the tree.
How does the index with included columns compare to an indexed view?
While include column indexes behave simmilarly in that SQL's optimizer will use them automatically when it is more efficent (in any edition not just Enterprise), an indexed view is clustered, and requires schema binding set on the view. Both would only have the non-key values in the leaf nodes. Both can have WHERE clause conditions, but an indexed view can be on more than one table while the include column indexes can obviously only be on one table.