SQL Server Filtered indexes use a filter predicate to index a portion of the table, a well-defined subset of data. They can be considered to be an optimized alternative to full-table indexes provided it is properly designed. Since filtered indexes will only index a subset of data, they require less storage, offer improved query performance while retrieving data from that subset of data and may/may not offer reduced maintenance overhead depending on how frequently that data is modified.
Here is a small example of using Filtered Indexes from BOL.
use AdventureWorks2008 go select * from Sales.SalesOrderHeader Go -- create a duplicate table select * into Sales.SOHdup 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' select * from sys.indexes 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], i.name, i.index_id, p.partition_number, 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 -- check performance with large data - do that on your own :)