Filtered Index – SQL Server 2008

Introduction:
Filtered index concept is new feature added from SQL Server 2008. A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.

Pros:
Improved query performance and plan quality
Since you are creating index for a specific subset of data’s SQL engine won’t perform full non-clustered index scan where it increases the query plan quality for the subset of data’s.

Reduced index maintenance costs
Filtered Index gets affected only when that particular subset of data gets affected there by reducing maintenance cost for the index.

Reduced index storage costs
Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.

Cons:
Filtered Index Not possible to Views
You cannot create a filtered index on a view. However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view.

Non clustered Index required
Consider your query will 60% of subset data’s so you can go for filtered index, if the remaining 40% depends outside the subset data then you need to create another full non-clustered index to make query plan to use this. If you have both full and filtered non-clustered index query optimizer is seeking full index only.

Syntax for Creating Filtered Index:

CREATE NONCLUSTERED INDEX IndexName
ON <OBJECT> ( COLUMN [ ASC | DESC ] [ ,...n ] ) 
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE  ]

Walk through Filtered Index:

To know more about filtered index usage we are going to walkthrough this topic with an example. I’m going to use CREDITCARD table (copied from Adventurework db and removed all the index for the table) from SAGAR database which has 19118 rows.

Case1: Selecting subset of data’s without Index

In this case CREDITCARD table doesn’t have any index.

You can find the operator cost is 0.164312 & IO Cost is 0.143203

Case2: Selecting subset of data’s with Full Non-Clustered Index

OK, lets create a full non-clustered index for CreditCard table and then check the cost.In this case we are creating a Non-clustered index for the full table

CREATE NONCLUSTERED INDEX [Full_nonclstered] ON [dbo].[CreditCard]
([ExpYear] ASC)INCLUDE ( [CardType],[CardNumber])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Now the cost is improved and operator cost is 0.03738 and IO cost is .03201

Case3: Selecting subset of data’s with Filtered Non-Clustered Index

Ok Lets create a filtered index on ExpYear column and check the cost.

CREATE NONCLUSTERED INDEX [Filtered_index] ON [dbo].[CreditCard]
([ExpYear] ASC)INCLUDE ( [CardType],[CardNumber])
WHERE ([ExpYear]>(2007))WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

You can see from the image above that cost required is reduced a great and the cost is 0.01515 and IO cost is 0.00979 there by increasing performance. Hence filtered index is best used when querying subset of data’s.

Case4: Selecting subset of data’s with Filtered Non-Clustered Index and Full Non-clustered Index.

In this case we are going to have both filtered and full non-clustered index for that table, lets see which index is query optimizer is utilizing.

Hmmm… Query optimizer is utilizing full non-clustered index only and it’s not using filtered index.

Indexed Views vs Filtered Indexes:

Filtered indexes have the following advantages over indexed views:

• Reduced index maintenance costs. For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.
• Improved plan quality. For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.
• Online index rebuilds. You can rebuild filtered indexes while they are available for queries. Online index rebuilds are not supported for indexed views. For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).
• Non-unique indexes. Filtered indexes can be non-unique, whereas indexed views must be unique.

Views vs Filtered Indexes

A view is a virtual table that stores the definition of a query; it has a broader purpose and functionality than a filtered index.The following table compares some of the functionality allowed in views with that of filtered indexes.

*For simple comparison logic in a predicate, see WHERE clause syntax in CREATE INDEX.

**For complex comparison logic in a predicate, see WHERE clause syntax for SELECT.

You cannot create a filtered index on a view. However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

Conclusion:

Filtered Index is a good feature in SQL Server 2005which helps in querying subset of data’s. You can create filtered index on a table where you will be mostly querying data’s based on subset of values. From case 4 it is understood that if you have both full and filtered non-clustered index for a table, query plan is pointing to full non-clustered index only which makes no use of filtered index. Hence select filtered index based on the query requirement.