SQL Server Filtered Indexes

Hi Friends,

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 :)