Filtered index in SQL Server 2008
Introduction
A filtered index in an optimised nonclustered index, specially designed to index a sub set of data i.e, a portion of rows in as table.
This is a new feature in SQL Server 2008 to improve query performance, reduce index maintenance cost and reduce index storage cost.
When to use filtered index
Suppose we have a Product_Order table and we do query on those records where shipping_date is not available i.e where shipping_date is NULL.
So if there are 10000 records in which 30% of the records having shipping_date NOT NULL then on adding filtred index on these recods will filter only 30% of
the total records so improving query performance, reducing index storage cost.
How to create filtered index
CREATE NONCLUSTERED INDEX Product_Order_ShippingDate
ON Product_Order(OrderId,Shipping_Date)
WHERE Shipping_Date IS NOT NULL ;
GO
Notes:
You can not create a cluster index with filter.
It can not be created on full text index.