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

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.

avatar image By mjswart 129 asked Jun 17, 2015 at 04:13 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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

sp.png (25.6 kB)
avatar image By SQLkiwi ♦ 6.6k answered Jun 17, 2015 at 04:43 PM
more ▼
(comments are locked)
avatar image Dean@SQLXL Jun 17, 2015 at 11:38 PM

Solid response SQLkiwi!

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x2

asked: Jun 17, 2015 at 04:13 PM

Seen: 255 times

Last Updated: Jun 17, 2015 at 11:38 PM