Why and How to create a Filtered Index in SQL Server 2008

When an index is built, every value in the index key is loaded into the index. In effect, each index is a mini-table containing all the values corresponding to just the columns in the index key. Therefore, it is possible for a query to be entirely satisfied by using the data in the index. An index that is constructed such that SQL Server can completely satisfy queries by reading only the index is called a covering index.

When an index is created, SQL Server generates a structure called a histogram that stores information about the relative distribution of data values within a column. The degree to which values in the column allow you to locate small sets of data is referred to as the selectivity of the index. As the number of unique values within a column increases, the selectivity of an index increases. The query optimizer chooses the most selective indexes to satisfy a query because a highly selective index allows the query processor to eliminate a very large portion of the table so as to access the least amount of data necessary to satisfy your query. Indexes with low selectivity and a low percentage of unique values are not considered by the query optimizer, but they still incur an overhead for write operations.

To handle the cases where significant skew exists in the data, SQL Server 2008 allows you to create filtered indexes. A filtered index is simply an index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table while allowing SQL Server to choose another method for the less selective range.

Filtered indexes have the following restrictions:

• They must be a nonclustered index.
• They cannot be created on computed columns.
• Columns cannot undergo implicit or explicit data type conversion.

Eg:

CREATE NONCLUSTERED INDEX idx_city2
ON Person.Address(City)
INCLUDE (AddressLine1, AddressLine2)
WHERE AddressLine2 IS NOT NULL

After going through many articles on the web, I found one article which I think is the best article on Filtered Indexes in SQL Server 2008. This article is a blog post by Randy Dyess from Solid Quality Mentors.