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. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. Filtered indexes can provide the following advantages over full-table indexes: Improved query performance and plan quality A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index. Reduced index maintenance costs An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics. Reduced index storage costs Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.
Here is a small example of using Filtered Indexes from BOL.
use AdventureWorks2008 go select *
from Sales.SalesOrderHeader Go -- create a duplicate table select *
from Sales.SalesOrderHeader Go -- check the table select *
from Sales.sohdup --check if there are any indexes sp_helpindex 'Sales.SOHdup' Go -- create a non clustered index create nonclustered index idx_NC_SOID on Sales.SOHdup(SalesOrderID) sp_helpindex 'Sales.SOHdup' GO -- using DMV SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID(N'AdventureWorks2008') , OBJECT_ID (N'Sales.SOHDup') , NULL , NULL , 'detailed'); GO sp_spaceused 'Sales.SOHdup'
WHERE object_id = object_id('Sales.SOHDup') -- EP means Execution Plan --select all records and see the EP SELECT salesorderID
FROM Sales.sohdup -- now we want to create a filtered index for the following query SELECT salesorderID
FROM Sales.sohdup WHERE salesorderID >= 58659 --first drop the exiting index DROP INDEX idx_NC_SOID ON Sales.SOHdup -- create a filtered index CREATE NONCLUSTERED INDEX idx_NC_SOID ON Sales.SOHdup(SalesOrderID) WHERE salesorderID >= 58659 Go -- now see the usage on index in the EP SELECT salesorderID
FROM Sales.sohdup WHERE salesorderID > 58659 -- now observe the size and stats for the filtered index SELECT i.[object_id]
, p.rows as [#Records]
, a.total_pages * 8 as [Reserved(kb)]
, a.used_pages * 8 as [Used(kb)] FROM sys.indexes as i INNER JOIN sys.partitions as p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units as a ON p.partition_id = a.container_id WHERE i.[object_id] = object_id('Sales.SOHDUP') -- AND i.index_id = 1 -- clustered index ORDER BY p.partition_number go
Try on large tables on your environment and see how it can improve your performance.