Filtered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.
The advantages of Filtered indexes are :
- Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
- The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
- The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
- The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.
No comments:
Post a Comment