Power of Filtered Indexes in SQL Server

Introduction

In this article, we will learn about Filtered Indexes in SQL Server. SQL Server 2008 introduced Filtered Index.

Filtered Indexes in SQL Server

Filtered Indexes is nothing but an optimized, non-clustered index. It helps us to improve query performance, reduce index maintenance costs, and reduce index storage costs compared to full-table indexes. Using Filtered Indexes, we can define a filter predicate, a WHERE clause, while creating the index. The B-Tree contains only those rows that satisfy the filter criteria used while creating the Filtered Index.

It is also possible to have an index built on a subset of the rows in the table and where the clause is used to determine whether the row in the table will be in the index.

Syntax

CREATE NONCLUSTERED INDEX idx_name_normal
ON TableName(ColumnName)
WHERE ColumnName = @ColumnValue

Example

In the following example, we are trying to create a Filtered Index on the Table "EmployeeDetails" and the column "DepartmentCode" with the value "IT":

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

DROP TABLE [dbo].[EmployeeDetails]

GO

CREATE TABLE [dbo].[EmployeeDetails](

               [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
               [EmployeeCode] [varchar](10) NULL,
               [EmployeeName] [varchar](50) NULL,
               [DepartmentCode] [varchar](10) NULL,
               [LocationCode] [varchar](10) NULL,
               [salary] [int] NULL,

 CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED

(
               [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT into EmployeeDetails(EmployeeCode, EmployeeName, DepartmentCode, LocationCode ,salary)

VALUES
('E0001', 'Jignesh', 'IT','GNR', 2000),
('E0002', 'Tejas', 'IT','GNR',  5000),
('E0003', 'Rakesh', 'QA','BVN', 4000),
('E0004', 'Bhavin', 'QA','BVN', 2000),
('E0005', 'Sandip', 'HR','ABD', 3000),
('E0005', 'Tarun', 'HR','ABD', 5000) 

CREATE NONCLUSTERED INDEX idx_EmpDetail_normal
ON EmployeeDetails(DepartmentCode)
WHERE DepartmentCode = 'IT'

Advantages of a Filtered Index

A Filtered Index improves query performance and execution plan quality because it is smaller than a full-table non-clustered index and has filtered statistics.
A Filtered Index reduces index maintenance costs compared with a full-table non-clustered index because the index is maintained only when the DML statement data is in the index.
Reduced index storage costs

Comparison with Index View

A Filtered Index is created on the columns of a specified table, whereas Index Views can be created on column(s) from multiple base tables.

An Index View can also use complex logic in the where clause, whereas a Filtered Index does not allow complex logic.

A Filtered Index can be rebuilt online, whereas Indexed views cannot be rebuilt online.

A Filtered Index can reduce index maintenance costs. The query processor uses fewer CPU resources to update it. Since Indexed Views are more complex, so the index can be more extensive and consume more CPU resources while updating it.

Both can only be created as a unique index.

When to Use Filtered Indexes?

Sparse columns that contain only a few non-NULL values.

Heterogeneous columns that contain categories of data.

Columns contain ranges of values such as amounts, time, and dates.

Table partitions that are defined by simple comparison logic for column values.

Conclusion

A Filtered Index is an optimized non-clustered Index which is one of the performance improvements in SQL Server, reducing the index storage cost and maintenance costs of indexes.