Why doesn't SQL Server use this filtered index for NULL values?
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.
2015-06-17 16:43:08
The bookmark lookup with the unnecessary predicate is a known optimizer limitation:
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:
Dean@SQLXL 2015-06-17 23:38:49
Solid response SQLkiwi!