Why doesn't SQL Server use this filtered index for NULL values?

mjswart 2015-06-17 16:13:23

I have a USERS table with a clustered primary key on UserId and a filtered index which contains rows with NULL middle names:

create table dbo.users
(
    UserId int identity 
      constraint pk_users primary key,
    FirstName varchar(100),
    MiddleName varchar(100),
    LastName varchar(100),
);
 
create unique nonclustered index ix_users_without_middlenames 
    on dbo.users(userid, LastName)
    where MiddleName IS NULL;
 
insert dbo.users(FirstName, MiddleName, LastName)
values ('A', 'A', 'A'),
       ('B', 'B', 'B'),
       ('C', null, 'C'),
       ('D', null, 'D');

Why doesn't SQL Server use the filtered index for this query:

select userid from dbo.users where MiddleName IS NULL;

It shows a TRIVIAL optimization level, but even this query:

select userid 
from dbo.users WITH (INDEX (ix_users_without_middlenames))
where MiddleName IS NULL;

uses a bookmark lookup to test for null.

SQLkiwi 2015-06-17 16:43:08
The bookmark lookup with the unnecessary predicate is a known optimizer limitation:

https://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output

The solution (and general best practice, given other known limitations) is to include the filtering column in the key or include portion of the index, for example:

 create unique nonclustered index ix_users_without_middlenames 
     on dbo.users(userid, LastName)
     INCLUDE (MiddleName)
     where MiddleName IS NULL;

This produces the expected execution plan in the trivial phase:

Expected plan

Dean@SQLXL 2015-06-17 23:38:49
Solid response SQLkiwi!
June 17, 2015
No Comments