Skip Navigation LinksHome > Categories > Code from a Category

Filtered Indexes in SQL Server 2008

User Name: codelecturer
Name: Mike Chauhan
Contact Me:
Home Page:
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1284
Add Date: 10/30/2012
In this article we will learn about Filtered Indexes in SQL Server 2008
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.

Limitations of Filtered Indexes

The important thing to note is that you cannot create a Filtered Index on a View. However, you can define a filtered index on the underlying table used by the view instead of creating an index on a view. This will also help in reducing the index storage space and maintenance costs. The following conditions needs to be met when you want to leverage Filtered indexes when views are created. The first condition is that the view should be referring to only one table. Secondly, the queries should not be referring any computed columns, UDT columns, Spatial Data Type column, and HierarchyID Data Type column. Finally, Filtered Indexes cannot be used in XML and Full Text Indexes.

Example on Filtered Index in SQL Server 2008
In this example we will be using HumanResources.Employee table which is available in AdventureWorks database of SQL Server 2008. Let us first find out the list of indexes which are available on HumanResources.Employee table by running the below TSQL.

Now let’s find out the list of employees who are having title as ‘FINANCE MANAGER’

You could see that in HUMANRESOURCES.EMPLOYEE table there is only one record which is having title as ‘FINANCE MANAGER’ out of 290 records. Now let us create a filtered index which will use the WHERE clause TITLE = ‘FINANCE MANAGER’ and check if there is any performance improvement by creating a Filtered Index. In the new query window press CTRL+M which will help you Include Actual Execution Plan and then run the below TSQL.

Once the query has executed successfully, click EXECUTION PLAN tab to compare the performance of the queries before and after the creation of Filtered Index on HUMANRESOURCES.EMPLOYEE table. You could see that in the first scenario when the query ran before the creation of Filtered Index, the index scan is 100% done on Clustered Index taking 25% of total cost of query execution. In the second scenario when the query ran after the creation of Filtered Index on HUMANRESOURCES.EMPLOYEE table, the index scan was 50% on Non Clustered Index and another 50% on the Clustered Index and the cost of query was just 19%. This is a significant improvement even thought the table is just having 290 rows.

From this example it’s clear that if Filtered Index is defined correctly, then it will result in significant improvement when created on larger tables to retrieve a very small percentage of data.

The below TSQL once executed will let you know the number of rows which will be resulted by the Filtered Index. You could see that in the above mentioned TSQL code we had created a new Filtered Index namely FI_DEPARTMENT.

You could see that the newly created FI_DEPARTMENT Filtered Index has only one row. This means that when you are executing the query to search for records where TITLE = ‘FINANCE MANAGER’, the databases engine uses the newly created Filtered Index their by improving performance considerably even for a small table like HUMANRESOURCES.EMPLOYEE in AdventureWorks database.

Before creating Filtered Indexes you need to first analyse the queries which are used by your application and how they identify the subset of data. If the subset of data returned is very small then it is ideal to create Filtered Indexes. Before creating Filtered Indexes in production a thorough investigation is a must.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.