or
sql-server
Josh Darnell
I'm considering creating a filtered index in my [copy of the Stack Overflow database][1].  Something like this, for example:

    CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
        ON dbo.Users (DisplayName)
        WHERE Reputation > 400000;

Should I always add the column in the filtering expression (`Reputation` in this example) to the key or includes for the index, or is having it in the filtering expression good enough?

[1]: https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/
Top Answer
Josh Darnell
# Yes!

For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes

The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.

## Key lookups when the query predicate doesn't match the filter expression

First of all, [the documentation][1] has this to say about including filter expression columns:

> - A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

So if you have an inequality filter expression like `Reputation > 400000`, but your *query* uses a predicate like `WHERE Reputation > 400000 AND Reputation < 450000;`, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.

Including the `Reputation` column in the index (key or includes) removes the need for this lookup.

See Erik Darling's post [Filtered Indexes: Just Add Includes][2] for additional details and an example of this situation.

Another example of this can be found in Paul White's answer here: [Unnecessary key lookup being performed while using filtered index][3]

## Key lookups when the filtering column is included in the resultset

The documentation goes on to say this:

> - A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).

## Poor row estimates when using equality expressions

There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan).  Including the filtering column can prevent these more-accurate estimates from being discarded.

More details, and an example, can be found in Paul White's answer here: [Incorrect row estimation given with a filtered index][4]

An additional example can be found here on dba.se: [Query using a filtered index but wrong estimated number of rows][6]

## Key lookups when using `IS NULL` in the filtering expression

Creating an index with a filtering expression that uses `IS NULL` can produce a completely unnecessary key lookup.  See this question, and the related bug report on SQL Server's feedback site: [Why filtered index on IS NULL value is not used?][5]

As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.

[1]: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-2017
[2]: https://www.brentozar.com/archive/2015/12/filtered-indexes-just-add-includes/
[3]: https://answers.sqlperformance.com/questions/2894/unnecessary-key-lookup-being-performed-while-using.html
[4]: https://answers.sqlperformance.com/questions/336/incorrect-row-estimation-given-with-a-filtered-ind.html
[5]: https://dba.stackexchange.com/questions/217046/why-filtered-index-on-is-null-value-is-not-used
[6]: https://dba.stackexchange.com/questions/208897/query-using-a-filtered-index-but-wrong-estimated-number-of-rows
Should the filtering column(s) always be in the keys / includes?
GeorgePalacios
Happy to take your lead in regards to the question itself though :)
GeorgePalacios replying to Josh Darnell
No I agree on that. And actually it doesn't affect the actions taken by the answer at all. It's more of a side-note that actually adding the explicit definition is unneccessary
Josh Darnell replying to GeorgePalacios
I failed to tag you in that last message, George.
Josh Darnell
If you are filtering on the clustering key for some reason, and include that in the index definition, it's a no-op essentially so maybe it doesn't matter.
Josh Darnell
I always struggle with stuff like this. This Q&A is focused on a specific issue related to filtered indexes, and making sure that filtering columns are included in the index itself.  Adding a note about key columns being automatically included potentially starts down a rabbit hole of describing how indexes work.
GeorgePalacios
I assume there's no difference in implementation though
GeorgePalacios
With filtered indexes
GeorgePalacios
I may do the same thing I did here: https://georgepalacios.com/13-should-i-add-the-clustering-key-to-a-non-clustered-index
Josh Darnell replying to GeorgePalacios
Oof, double whammy.
GeorgePalacios
(AND skype for business)
GeorgePalacios
Akchooally we're using teams now
Josh Darnell replying to GeorgePalacios
Skype for business user detected
GeorgePalacios replying to Josh Darnell
(Y)
Josh Darnell replying to GeorgePalacios
That's an interesting point!  I'll think about how to incorporate it.
GeorgePalacios
If the filter column is the same as the key column of the clustered index *
GeorgePalacios
That sentence didn't make sense
GeorgePalacios
@Josh 
GeorgePalacios
Small caveat - if the filter column is the clustered index it doesn't matter if you add it or not.