SQL Hosting News – How to Filtered Indexes in SQL Server?

Peter Cole | August 6, 2015 | SQL 2012 Hosting | No Comments

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 enhance query performance, reduce index maintenance prices, and reduce index storage costs compared to full-table indexes.

It is also possible to have an index that is built on a subset of the rows in the table and where the clause used to confirm 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

CREATE NONCLUSTERED INDEX idx_name_normal

ON TableName(ColumnName)

WHERE ColumnName = @ColumnValue

Example
In the following example we have a tendency to try to make 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', 'Peter', 'IT','GNR', 2000),
('E0002', 'Scott', 'IT','GNR',  5000),
('E0003', 'James', 'QA','BVN', 4000),
('E0004', 'Rebecca', 'QA','BVN', 2000),
('E0005', 'Raymond', 'HR','ABD', 3000),
('E0005', 'Suzan', 'HR','ABD', 5000)

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

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

DROP TABLE [dbo].[EmployeeDetails]

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', 'Peter', 'IT','GNR', 2000),

('E0002', 'Scott', 'IT','GNR', 5000),

('E0003', 'James', 'QA','BVN', 4000),

('E0004', 'Rebecca', 'QA','BVN', 2000),

('E0005', 'Raymond', 'HR','ABD', 3000),

('E0005', 'Suzan', '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’s 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 the DML statement information is in the index.
Reduced index storage costs

Comparison with Index view

A Filtered Index is formed on the columns of a specified table whereas Index Views are often created on column(s) from multiple base tables.
an Index view can also use complex logic in the where clause whereas a Filtered Index doesn’t enable complex logic.
A Filtered Index can be remodeled on-line whereas Indexed views can’t be rebuilt on-line.
A Filtered Index will reduce index maintenance costs. The query processor uses fewer C.P.U. resources to update it. Since Indexed Views are more complex, the index can be larger and will consume a lot of C.P.U. resources whereas change 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.

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

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Related Posts

About The Author

Pete