Performance improvements with filtered indexes in SQL 2008

In SQL 2008 filtered indexes are introduced. A filtered index allows us to create a filter to index a subset of rows within a table. A filtered index will:

1. Improve query performance. Statistics are more accurate which can result in better query plans. 
2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
3. Reduce index storage costs.

Filtered indexes can be very useful if you have one of the following situations:

1. Columns which contains mostly NULL values and where queries retrieve only the rows where the data is NOT NULL. 
2. When you want to enforce uniqueness for a set of values. With a filtered index you can create a unique index for the set of values. For instance
   CREATE UNIQUE NONCLUSTERED INDEX CustomerID_NOTNULL ON Accounts (CustomerID) WHERE CustomerID IS NOT NULL 
3. When analyzing data on a specific data range. For instance you have a table with records for the last 4 years (2006,2007,2008,2009). You are analyzing only the year 2009. A filtered index will look like
   CREATE INDEX YEAR2009 ON Transactions (Year,Period, Amount) WHERE Year = 2009

To see the difference in index sizes on a table create a normal index and a filtered index. For instance:

1. CREATE INDEX YEAR ON Transactions (Year,Period, Amount)
2. CREATE INDEX YEAR2009 ON Transactions (Year,Period, Amount) WHERE Year = 2009

Use next script to see the difference in index size:


DECLARE @TableName VARCHAR(100)

SET @TableName = 'fill in the tablename'

SELECT db.index_id,si.name, IndexSizeKB = SUM(page_count * 8)
FROM sys.dm_db_index_physical_stats(db_id(),object_id(@TableName),NULL,NULL,'DETAILED') db
INNER JOIN sys.indexes SI ON SI.index_id = db.index_id AND SI.object_id = object_id(@TableName)
GROUP BY db.index_id ,si.name
ORDER BY db.index_id desc,si.name