Filtered Indexing In SQL Server 2008

In this article, I describe about Filtered Indexing in SQL Server which is a new feature in SQL Server 2008.
  • 1801

SQL Server 2008 explores a new feature Filtered Index which is a  optimized non-clustered index with a WHERE clause. It is quite different from other indexes. With filtered indexes, it's a possible to have index that are created on a well-defined subset of data rather than the entire set of data in SQL Server. It is a major advantage on very large tables where  most queries are only interested on a specific subset of data.

Syntax

CREATE NONCLUSTERED INDEX index_name

ON TableName(Column_Name)

WHERE Column_Name = @Column_Value

For Example,

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student1Details]') AND type in (N'U'))

DROP TABLE [dbo].Student1Details

GO

create table Student1Details(

               [ID]  int NOT NULL,

               [Name] [varchar](50) NULL,

               [Branch] [varchar](10) NULL,

               [Location] [varchar](10) NULL              

 constraint [PK_Student1] primary key clustered

(

               [ID] asc

)) on [PRIMARY]

insert into Student1Details

select 1, 'Nitin', 'CS','IND' union all

select 2, 'Ravi', 'EI','ENG' union all

select 3, 'Tim', 'ME','US' union all

select 4, 'Rick', 'ME','IND' union all

select 5, 'Rakesh', 'CS','ABD' union all

select 6, 'Tarun', 'ME','IND' union all

select 7,'Raushan','IT','IND'

select * from Student1Details

select ID,Name,Location from Student1Details

where Branch='ME'

CREATE NONCLUSTERED INDEX index_student1

ON Student1Details(Branch)

WHERE Branch = 'ME'

select ID,Name,Location from Student1Details

where Branch='ME'

Output:

 filteredIndex.jpg

 

© 2019 DotNetHeaven. All rights reserved.