Include Filtered Index Columns

Erik Darling shows how to make filtered indexes a bit more robust:

But why oh why didn’t SQL use my filtered indexes for even smaller subsets of the filter condition? It seemed insane to me that SQL would know the filter for the index is on (x > y), but wouldn’t use them even if (z > x).

The solution was to put the filtered column in the include list. This lets SQL generate statistics on the column, and much like getting rid of the predicate key lookup, allows you to search within the filtered index subset for even more specific information.

Filtered indexes are as useful as they are mercurial.

Related Posts

Memory-Optimized Table Maintenance

Ned Otter has a great post looking at what you can and cannot do with memory-optimized tables containing certain types of indexes: Now, let’s attempt to create a NONCLUSTERED COLUMNSTORE INDEX: ALTER TABLE dbo.InMemADD ADD INDEX NCCI_InMem NONCLUSTERED COLUMNSTORE (col1); Msg 10794, Level 16, State 76, Line 76 The feature ‘NONCLUSTERED COLUMNSTORE’ is not supported […]

Read More

Index Rebuilds Reset DMV Counters

Clive Strong notes that an index rebuild will reset certain DMV counters: As it transpires, an index rebuild will reset the counters for this index within the sys.dm_db_index_usage DMV and this is potentially very dangerous unless you are aware of this. Normally, we determine whether or not an index is in use by looking at […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031