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.
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
The general syntax for a Filtered Index is:
CREATE NONCLUSTERED INDEX
ON
Include (
WHERE
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:
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.