Hugo Kornelis (3/30/2014)

---

Carlo Romagnano (3/21/2014)

---

Remember that "Filtered indexes" are used only if the WHERE clause matches or is compatible with the filter.

View http://www.sqlservercentral.com/questions/T-SQL/105826/

And even then, they are often not used at all.

I just uploaded slide deck and sample code of my "Advanced Indexing" talk to http://www.sqlsaturday.com/viewsession.aspx?sat=275&sessionid=18987 - the reason I mention this is that the sample code includes a repro where SQL Server does not use the filtered index, even though it would be a lot cheaper (by head, 1100 vs 80 logical reads, as you can see when hinting the filtered index). Adding an INCLUDE clause with the column used in the filter causes the optimizer to pick the filtered index more often, but still not all the time.

Also see http://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output (and maybe add a vote or two).