I was asked if it is possible to create an filtered index with a WHERE col IN (…) clause and then use WHERE col = n with n being one of the values of the (…) list

So let’s create a small sample

create table idx
(
ID int NOT NULL IDENTITY(1,1) primary key clustered,
Data int
)
insert into idx (Data) values (1)
go 100
insert into idx (Data) values (2)
go 2
insert into idx (Data) values (3)
go 2

create index idx1 on idx(ID)
where Data in (2,3)

select ID from idx where Data in (2, 3) -- Index is used

select ID from idx where Data = 2 -- Table scan

select ID from idx where Data in (2, 3) AND Data = 2 -- Table scan

-- But that’s not because SQL is not smart we created the index wrong

drop index idx1 on idx

create index idx1 on idx(ID)
INCLUDE (Data) -- if the data we filter on is not in the index SQL has to do a lookup on the table
where Data in (2,3) -- and therefore chooses to make an full table scan

select ID from idx where Data in (2, 3) -- Index is used

select ID from idx where Data = 2 -- Index is used !!!! THATS what we wanted

select ID from idx where Data = 1 -- Table scan as expected