SQL Server 2008 introduces a new feature called Filtered Index. A Filtered Index is an optimized form of non clustered index. It is basically useful to cover those queries which return a small percentage of data from a well defined subset of data within a table. In the earlier versions of SQL Server, Developers and Database Administrators have always faced performance issues when dealing with large amount of data in the tables. In order to get the best performance for your queries in the previous SQL Server Versions you might have to partition the table as in SQL Server 2005 or archive the data periodically in the earlier SQL Server Releases.
Advantages of Filtered Indexes
Filtered Indexes is one of the greatest performance improvement introduced in SQL server 2008. A Filtered Index allows us to create a filter to index a subset of rows within a table. i.e., developers or database administrators can create non clustered indexes with a WHERE clause.
- A very well designed filtered index will help you improve query performance on very large tables; this will also generate a better execution plan as it will be much smaller than the full table non clustered index. It is more accurate than a full table non clustered index because it will only cover those rows available in the filtered index WHERE clause
- A Filtered index will help you reduce the index maintenance costs as it is smaller and is maintained only when Data Manipulation Language (DML) statements affect the data in the index. It is better to have large number of filtered index, especially in scenarios when you know that the data on which filtered index is created is changed very less frequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the filtered index reduces the cost of updating the statistics
- Another major advantage of creating a Filtered Index is that it will reduce the disk storage space for non clustered indexes when a full table index is not required. Developers or Database Administrators can replace a full table non clustered index with multiple filtered indexes without significantly increasing the disk storage space for the indexes
When to Use Filtered Indexes
Being a Database Administrator or Developer you need to very well understand what queries are used by the applications and how they identify the subset of data. Ideally we can say that Filtered Index is an optimized non clustered index (Filtered Index can only be created as a non clustered index on a table), which is best suited for those queries that select a very small percentage of rows from a given table. Some of the examples of data which can be well defined subsets could be columns with NULL values or a column which has distinct range of values. Reduction in Filtered Index maintenance cost can be seen when the number of rows in the Filtered Index is very small when compared to a full table index. If the filtered index includes most of the records in the table then it will cost more to maintain a Filtered Index than a Full Table Index. So a DBA or a developer needs to be very careful in analysis the best and useful WHERE clause which should be used in when creating a Filtered Index. Filtered Indexes basically can be created on one table and it will improve the simple comparison operation. If your application requires a filter expression which refers to many database tables or has a very complex logic then the best solution is to create a view. In SQL Server 2008 you can create 999 non clustered indexes; however this doesn’t mean that you should create as many non clustered indexes as it will create performance impact when data gets changed often within the tables.