Sql-server – Included Column versus Filtered Index

So my question is, is there any difference in using the suggested index above, or what I think is a better alternative...

The missing-index suggestions made by the optimizer are opportunistic and relevant only to the particular query concerned. The optimizer goes through an index analysis phase, where it might note the absence of a covering index it didn't find. These suggestions are not intended to be a replacement for a full workload-representative DTA session, much less proper index design by a skilled database practitioner based on wide knowledge of the data and critical queries.

The suggestions should always be reviewed, as you have done, to ensure an optimal set of indexes for all queries is created - not one covering index per query as could be the case if the suggestions were followed literally.

There are naturally implications when widening the keys of an index compared with using INCLUDE column, some of which have been noted by others. I personally prefer to INCLUDE the clustering keys explicitly where they are useful. Clustered indexes can be changed, and it is rare that the person performing this change would check to see if any queries were relying on the implicit behaviour.

Changing columns from INCLUDE to keys may also affect update query plans (overall shape and Halloween Protection requirements) and there are logging implications where keys of an index might change too.

I would probably choose to modify the suggestion as you have done, but I would be careful to validate update (= insert/update/delete/merge) query plans for the affected table.

Very interesting approach. My upvote for the creativity.

Since you reclaimed the space, I assume the original indexes are no longer in place? The downsides of filtered indexes then are:

• Too many of them may cause the search space of the optimiser to grow too large, leading to poor query plans as the optimiser times out
• There are several situations where a filtered index will not even be considered, even though the non-filtered equivalent would be. Notably, this can happen when you get a hash join on the indexed column or if you try to ORDER BY the column (without a filter)
• Query parameterisation doesn't work with filtered indexes (see: http://www.sqlservercentral.com/blogs/practicalsqldba/2013/04/08/sql-server-part-9-filtered-index-a-new-way-for-performance-improvemnt/)

In practical terms, this means that you have to be extremely careful with filtered indexes as they will often result in horrible query plans. I would not go so far as to call them useless, but I view them as an addition to traditional indexes, not as a replacement (as you are trying to do).