Filter INDEX is a special type of INDEX available from SQL Server 2008 on wards. It is a non-clustered INDEX with where clause. Normal INDEX is used to create data on full set of data, but in case of filter INDEX you can create INDEX on some set of data.
Advantage of Filter INDEX
- It requires less amount of maintenance.
- It requires less storage space.
- Improve query performance.
Points to be remember
- Applicable only for Cluster Index.
- Feature not available in SQL Server 2005.
- For views only Persisted Views is applicable.