The filtered column index is one of the enhancements of the non-clustered index provided in Microsoft SQL Server 2008, and it is still available in SQL Server 2012. We can consider a simple non-clustered index with the
WHERE clause, as a filtered index. A well-defined filtered index reduces maintenance cost and index storage, and improves query performance.
Actually, a non-clustered index, whether it is a covering or include index, indexes all the rows available in a table, whereas a filtered index indexes only those rows that meet the criteria given in the
WHERE clause of the
CREATE INDEX command. This is the main reason that a filtered index needs low storage and displays improved performance.