Filtered Index is a new feature in SQL SERVER 2008 used to index a
portion of rows in a table which improves query performance, reduce index maintenance
costs and reduce index storage costs compared to full-table indexes.
If we have table with thousands of records and we are only concern with very few rows in our query we should use Filtered Index.
When we see an Index created with some filter i.e. WHERE clause then it is a FILTERED INDEX.
Consider these points while creating Filtered Index:
- They can be created only as Nonclustered Index
- They can be used on Views only if they are persisted views.
- They cannot be created on full-text Indexes.
Example:
Have a look at the Execution Plan and compare the performance before and after the Filtered Index!!
If we have table with thousands of records and we are only concern with very few rows in our query we should use Filtered Index.
When we see an Index created with some filter i.e. WHERE clause then it is a FILTERED INDEX.
Consider these points while creating Filtered Index:
- They can be created only as Nonclustered Index
- They can be used on Views only if they are persisted views.
- They cannot be created on full-text Indexes.
Example:
CREATE NONCLUSTERED INDEX NCI_DeptON HR.Emp(EmpID)WHERE Title= 'Manager'Have a look at the Execution Plan and compare the performance before and after the Filtered Index!!
No comments:
Post a Comment