Advertisment

Filtered Indexes in SQL Server 2009

author-image
PCQ Bureau
New Update

SQL Server 2008 comes with quite a few new features that have more stress on query performance such as filtered index. In this article, we will see how exactly filter indexes help SQL developers and DBAs and also its advantages when compared to full table non-clustered indexes.

Advertisment

Filtered index is basically a non-clustered index and as suggested by the name allows us to filter the subset of a data with a where condition. For example, say you have a table with around 90% Null values and 10% non-null values for a particular column having frequent queries on that column. In a large dataset, even though you might be able to improve the performance with the help of a full table non-clustered index, you might still not be able to improve query performance significantly as the dataset gets updated in future. Now, this is where filtered index comes as a great help.

Following points should be remembered while going for a filtered index:

  • Can be created on columns of a particular table with a non-clustered index
  • Cannot be used with Where clause having complex logic such a 'Like' clause
  • Cannot be created on full text indexes
  • Cannot be created on a view but can be defined on a table on which the view is based
Advertisment

The general syntax for a Filtered Index is:

CREATE NONCLUSTERED INDEX

ON ()

Include ()

WHERE

Advertisment

Example

Let us say that a store owner having stores at multiple locations wants to analyze product inventory in all stores. He wants to check if the quantity reaches below 10 for a product in the product inventory and its corresponding details. Following is a simplified query to check this using ProductInventory table.

USE AdventureWorks

GO

SELECT * FROM Production.ProductInventory

WHERE Quantity<10

Now let us create a full table non-clustered Index on ProductID and another non-clustered index on ProductID but with a where condition as shown below:

Advertisment

CREATE NONCLUSTERED INDEX IX_ProductInventory_ProductID

ON Production.ProductInventory(ProductID)

CREATE NONCLUSTERED INDEX FI_ProductInventory_ProductID

ON Production.ProductInventory(ProductID)

WHERE Quantity<10

To illustrate more clearly how exactly filtered index improves our query performance, we will see both I/O statistics and execution plan as well.

CHECKPOINT

DBCC DROPCLEANBUFFERS -- Flushing the changes to the disk and clearing the SQL Server buffer cache

GO

SET STATISTICS IO ON

SELECT * FROM Production.ProductInventory WITH(INDEX(IX_ProductInventory_ProductID))

WHERE Quantity<10

SELECT * FROM Production.ProductInventory WITH(INDEX(FI_ProductInventory_ProductID))

WHERE Quantity<10

SET STATISTICS IO OFF

Conclusion

Thus, filtered indexes improve the query performance where there is a need to have a small subset from a large subset. Filtered indexes come with advantages such as reduced index storage and maintenance cost, improved query performance and execution plan. Still, it is always helpful to do a thorough analysis of the data as per the requirement of the data before implementing in production environment.

Advertisment