Why use the Where clause when creating an index?

Question

When I read this book,

I found that I can create an index that has a where condition.

I found that we can create index like this

  Create index `<index_name>` on `<table_name>`

Or

  Create index `<index_name>` on `<table_name>` Where (`<Conditions>`)

But I do not know what the benefits are?

What are the benefits of a filtered index ?

Which of columns most be use in Where conditions ?

Answer 1

We have a few large tables in some of our databases where there are particular columns that are more often than not null and most of the queries that access these tables are only selecting data from the table where that particular column is not null.

Without a filter on the index, we have to index the entire table, every row, including the ones that we are never going to access with our queries.

With the filter on the index, the queries run much faster as the predicate on the query matches the filter on the index, hence the optimizer selects the much much smaller index to be used.