Filtered indexes Part2 - SQL Server 2008

To visit part 1 click here

Some benefit of filtered indexes
1. It has less performance hit. Since only data qualifying in the where condition are used , less data inserted/updated/deleted in index pages.

2.It uses less space.

When to use filtered indexes
When rows qualifying in the where clause is less than 50% of over all rowcount we should use filetered indexes.
Sparse columns are best example to be used for filtered indexes, because in general columns where more than 50% of value is null are used as sparse column.

Lets take one example


create table customer_master
(customer_id int,
customer_name varchar(100),
country varchar(100) sparse null)


insert into customer_master values(1,'abc','usa')
insert into customer_master values (2,'def','usa')
insert into customer_master values (3,'ghi','india')
insert into customer_master values (4,'jkl',null)
insert into customer_master values (5,'mno',null)
insert into customer_master values (6,'pqr',null)


create index idx_customer_master_country
on customer_master (country,customer_id)
where country is not null

select customer_id from customer_master where country = 'usa'
select customer_id from customer_master where country = 'india'




Here if we look at execution it uses index.