User Id :    Password :      New Member   Forgot Password  
 
Filtered index in SQL Server 2008
Description 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.   No. of Views     2657
  Rating     0
Author Najmul Hoda   Posted On     29 May 2011
Tags SQL SERVER,General    

Sample Code   Download Code

 

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.
 

 

About Author

About Author He is a team lead, having 8+ years of experience. He loves writing technical article for .Net developers. Najmul Hoda
No Photo
 
Country United States
Company BirlaSoft
Home Page http://www.magixchat.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

   
Write your comment here.
Comment
Verification Code