Learn T-SQL Querying

Filtered indexes

It is also possible in SQL Server to create an index on a subset of the rows in a table based on some predicate. This is called a filtered index, as the rows on which the index is built have already been filtered by a predicate at the time the index was created. Filtered indexes can be useful because they may be much smaller than regular indexes and thus more efficient. They also have the benefit of pre-filtering common predicates so that they don't have to be applied at query time.

An example of where a filtered index might be useful is a database schema that supports soft deletes. A soft delete is where, rather than deleting an existing row when it is no longer needed, it is marked as deleted using some sort of Boolean ...