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