When I create the test case below, the filtered index is always chosen, even on the different versions of SQL Server with different configuration.
create table #t (id int not null identity(1,1) primary key clustered ,value varchar(10) not null default (convert(varchar(10), convert(varchar(36), newid()))) ,status tinyint default (0) ) insert into #t (value, status) values (default, 3), (default, 2) -- repeat this however you like to populate select * from #t as t create index idxT_Status on #t (status) include (value) create index idxT_StatusF on #t (status) include (value) where status = 2 create procedure #p as begin insert into #t (value, status) select value, 33 from #t where status = 2 update t set status = 22 from #t as t where status = 2 end select * from #t as t where status = 2 begin tran insert into #t (value, status) select value, 33 from #t where status = 2
However, I have a very similar scenario in the live application which I am working with where in the presence of the full index, the filtered index is ignored. If I drop the full index, then the plan happily chooses the filtered index. I totally get it that with only about 20,000 records in the table it makes very little difference in terms of execution time, which is borderline negligible 0-16 ms with full or filtered index, just trying to get to the bottom of it.