Filtered index part -1 , SQL 2008

Along with sparse columns SQL Server 2008 comes with two more advanced features

1.Filtered indexes
2.Filtered statistics



What is filtered indexes?
Filtered indexes can be explained as index with where clause in create index statement.
Suppose we have customer data and we are interested in customer whoses country is U.S.A.
We are not interested in other customer from other countries.
Here we can create filtered index for country U.S.A only.

Lets create a table customer_master

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

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','india')
insert into customer_master values (5,'mno','usa')
insert into customer_master values (6,'pqr','china')

create index idx_customer_master_country_usa
on customer_master (country,customer_id)
where country = 'usa'


select customer_id from customer_master where country = 'india'

Execution plan Query1


select customer_id from customer_master where country = 'usa'

Execution plan Query2



Here we can see that in second query it uses index because country = 'usa'

To visit part 2 click here