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     2493
  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 .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.com

Rate this article

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

Comments

     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by