Unnecessary key lookup being performed while using filtered index.
SELECT p.FirstName, p.LastName FROM Person.Person p WHERE PersonType = 'SC' AND p.ModifiedDate > '2008-01-01' AND p.EmailPromotion = 1;
For the following non filtered index, SQL Server does not do a key lookup, since all the information required to execute the query is available in the index.
CREATE NONCLUSTERED INDEX IX_Test_Filtered_1 ON [Person].[Person] ([PersonType],[ModifiedDate],EmailPromotion) INCLUDE ([FirstName],[LastName])
But, SQL Server does a key lookup on Person table when the following filtered index is used, which increased table reads significantly.
CREATE NONCLUSTERED INDEX IX_Test_Filtered_2 ON [Person].[Person] (EmailPromotion) INCLUDE ([FirstName],[LastName]) WHERE (PersonType = 'SC' AND ModifiedDate > '2007-01-01')
I am forcing appropriate index usage using index index as follows:
SELECT p.FirstName, p.LastName FROM Person.Person p WITH( INDEX(IX_Test_Filtered_2)) WHERE PersonType = 'SC' AND p.ModifiedDate > '2008-01-01' AND p.EmailPromotion = 1; SELECT p.FirstName, p.LastName FROM Person.Person p WITH( INDEX(IX_Test_Non_Filtered_1)) WHERE PersonType = 'SC' AND p.ModifiedDate > '2008-01-01' AND p.EmailPromotion = 1;
I am trying to use a filtered index on a very large table and would like to get rid of this key lookup.
To check if the value of ModifiedDate in each row meets the query filter, the query processor must look up ModifiedDate in the clustered index. You can avoid this by including ModifiedDate in the keys or INCLUDE list of the filtered index definition. For example:
CREATE NONCLUSTERED INDEX IX_Test_Filtered_2 ON [Person].[Person] ([EmailPromotion], [ModifiedDate]) INCLUDE ([FirstName], [LastName], [PersonType]) WHERE (PersonType = 'SC' AND ModifiedDate > '2007-01-01');
There are many good reasons to always include filter condition columns in the keys or include list of a filtered index.