Filtered Indexing In SQL Server 2008
Posted in
SQL | Index February 08, 2020
In this article, I describe about Filtered Indexing in SQL Server which is a new feature in SQL Server 2008.
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: