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 ?

Alleyn

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.

Bloomery

Answer 2

The obvious reason to do it is to make the index more selective, or to use it to apply constraints:

Create unique index IX_Contacts on Contacts(User_ID) Where (IsDefault=1)

Says that, for each User_ID, they may have one, and only one, default contact in the Contacts table - but they can have as many non-default contacts as other constraints in the system will allow them to create.

Similarly, a query to try to locate the default contact for a user is far more likely to use this index because it is quite narrow and selective - if only 10% of contacts are the defaults for a user, then this index will be far smaller than an index on (User_ID,IsDefault) (which could also satisfy such a query)

Ecbolic