Should the predicate from a filtered index be in the front or back of the index?

Question

When creating a filtered index:

CREATE INDEX index_name
ON table_name ( column_list )
WHERE predicate;

It's my understanding you need to have the column(s) from the predicate also in your column list; but when I test this, I can't seem to spot any differences between the following designs (and a few other permutations of them). The query analyser seems to pick the same index each time.

Scenario 1: Predicate column in the front of the list of columns:

CREATE INDEX index_name
ON table_name ( predicateColumn, column_list )
WHERE predicate;

Scenario 2: Predicate column at the end of the list of columns:

CREATE INDEX index_name
ON table_name ( column_list, predicateColumn )
WHERE predicate;

Scenario 3: Predicate column in the included list of columns:

CREATE INDEX index_name
ON table_name ( column_list )
INCLUDE ( predicateColumn, column_list )
WHERE predicate;

Scenario 4: Predicate column completely omitted:

CREATE INDEX index_name
ON table_name ( column_list )
INCLUDE ( column_list )
WHERE predicate;

I would have thought that Scenario 1 would be the correct one, but would appreciate any insights that could be shared.

My table that I'm testing on has a few hundred thousand records in it, so I would have imagined tht I'd at least see some kind of difference but they all seem to result in the same values.

I've done a lot of searching, and found questions around filtered indexes, but none that actually addresses this specific question.

Closing summary

Based on the feedback, I would personally lean towards scenario 1 or 3, depending on the use case.

• 3 will give you covering and negate a potential unneeded lookup.
• 1 will give you the above and also additional support if your predicate can differ from the statement that could be written.

I would personally lean towards using 1 as a generally recommended rule.

Answer 1

There are certain circumstances when the compiler will use a filtered index, but still add an unnecessary predicate, and therefore if the column is ommitted completely then it could end up with an unnecessary Key Lookup.

If you just want to avoid that issue then adding to the INCLUDE is enough. There is no need to increase the index key size for that.

However, if your predicate covers multiple values (using IN) then you may want to add to the index key anyway. The exact key ordering would depend on what other predicates you have.