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
    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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.