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

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

3 Comments on “SQL Server Filtered Indexes”

  1. Thanks for sharing Amit.

    This is something new and very good.

    Nice to see MS has done some good research and collected a lot of inputs for the new features in 2008.

    does this feature exist in 2005 as well?

Leave a Reply

Your email address will not be published.