SQL | Filtered Indexes
A SQL Server filtered index is a non-clustered index that allows us to apply specific conditions to cover a subset of rows in the table.
This can save on space, improve on query performance and reduce maintenance overhead as the index is much smaller.
For Example, we have a student table and in our web application we only requests the active students data. In this case, create an index for the only active student rows is very reasonable than creating an index for whole rows of the table.
Create Filtered Index
A filtered index is created simply by adding a WHERE clause to any non-clustered index creation statement.
Confirm Index Usage
The following query should use our newly created index.
