Filtered Indexes in SQL Server

Introduction

SQL Server 2008 introduced Filtered Index. It is nothing but an optimized non-clustered index. 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 help us to improve query performance, reduce index maintenance costs, and reduce index storage costs compared to full-table indexes.

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

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'

Advantage 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, the index can be larger and will 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 that 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 reducing maintenance costs of indexes.