Filtered index in Sql Server
A SQL Server filtered index is a non-clustered index that enables the application of certain conditions to encompass a subset of data inside the table.
Deepak Talwar
The filtered index concept comes into play when our applications just need to query a portion of a table's rows. A filtered index is a non-clustered index with a predicate that lets us select which rows to include in the index. For example our ecommerce application request only the data of active products from the product table. In this instance, establishing an index for solely the active product rows is more rational than constructing an index for the entire table rows
Benefits of filtered indexes:
Filtered indexes can save spaces, especially when the index key columns are sparse. Columns with a lot of NULL values are considered sparse. It also reduces maintenance costs because only a part of the data rows will be changed when the data in the related table changes, rather than all of them.
Following query enables us to obtain information regarding the filtered index that we have created.
