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

Don’t Forget Those Paused Indexes

Arun Sirpal tries to create a new index on his Azure SQL Database: I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code: CREATE NONCLUSTERED INDEX [dbo.NCI_Time] ON [dbo].[Audit] ([UserId]) INCLUDE ([DefID],[ShopID]) Msg 10637, Level 16, State 3, Line 7 Cannot […]

Read More

Notes On Automating Automatic Indexing

Grant Fritchey shares with us some of his findings with automatic indexing on Azure SQL Database: What you’ll notice is that several of the queries are filtering on the FirstName column. There’s no good index there. If you look at the execution plans for those queries you’ll also note the Missing Index suggestion. That suggestion […]

Read More

Categories

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