SQL - Filtered Index

This feature has been introduced with SQL Server 2008. Filter Indexes are most powerful feature of SQL because they provide opportunities for much more efficient use of I/O and they have great potential.  

A Filtered Index is an optimized non-clustered index which allows us to define a filter predicate with WHERE clause whenever creating the index. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

In very simple words, we can say that “Filtered indexes allow us to create an index on a subset of data using a filtering predicate”.

An optimized non-clustered index offers several benefits over a full table non-clustered index such a given below:

Improved query performance and plan quality

A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

Reduced index maintenance costs

A filtered index reduces index maintenance costs compared with a full-table non-clustered index because it is smaller and is only maintained when the data in the index is changed.

Reduced index storage costs

By using a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. We can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Design Considerations

When a column only has a small number of relevant values for queries, we can create a filtered index on the subset of values.

When a table has heterogeneous data rows, we can create a filtered index for one or more categories of data. This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table.

Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

Syntax of Filtered Index and Performance

To see the performance of the filtered index, we will use the [HumanResources].[Employee] table from [AdventureWorks2012] database of Microsoft such as-

USE [AdventureWorks2012] GO SELECT                  [BusinessEntityID]                 ,[JobTitle]   FROM                 [HumanResources].[Employee]   Where                 JobTitle= 'Marketing Manager' BusinessEntityID JobTitle 16 Marketing Manager

Execution plan is showing the Query cost as 100% as given below:

Now, we are going to create a Filtered Index on the table and execute the queries as shown below:

USE [AdventureWorks2012] GO ---- Create Filtered Index CREATE NONCLUSTERED INDEX Ind_Employee_JobTitle ON [HumanResources].[Employee](BusinessEntityID) WHERE JobTitle= 'Marketing Manager' ---- select data after creating filtered index SELECT                  [BusinessEntityID]                 ,[JobTitle]   FROM                 [HumanResources].[Employee]   Where                 JobTitle= 'Marketing Manager'

After creating the filtered index on Employee table, the Index scan is 50% on Clustered Index and 50% on Nonclustered Index then Query Cost is 50% as shown below:

In SQL Server 2016, filtered indexes and indexes with included columns can be defined within the table definitions which were not possible in previous versions.

Use Demo Go /*SQL Server 2016 allows filtered indexes*/ Create Table SalesOrder ( OrderId Int NOT NULL, OrderDate Date, CustId Int, ----- Column with filtered indexes definition Qty Int INDEX ind_ord UNIQUE WHERE Qty>10, Amt float ) ----- Insert values into the table INSERT dbo.SalesOrder (OrderId, OrderDate,CustId, Qty,Amt) VALUES (20101, '01/23/2016',341, 9, 123.45), (20102, '01/31/2016',347, 12, 223.45),(20103, '02/11/2016',341, 8, 423.45), (20104, '02/22/2016',345, 25, 323.45),(20105, '03/04/2016',352, 8, 121.45), (20106, '03/12/2016',241, 35, 1123.45),(20107, '03/22/2016',544, 32, 1213.45) ---- Pull values from the table select OrderId, OrderDate,CustId, Qty,Amt from dbo.SalesOrder Query Execution plan without filtered data: ---- Pull values from the table select OrderId, OrderDate,CustId, Qty,Amt from dbo.SalesOrder ---- Data filter condition where Qty>10 Query Execution plan with filtered data:

Difference between Filtered Index and Indexed View

Filtered indexes have the following advantages over indexed views-

Criteria	Filtered Index	Indexed Views
Maintenance Costs	Reduced index maintenance costs because the query processor uses fewer CPU resources to update a filtered index	The query processor uses more CPU resources to update a Indexed View.
Plan Quality	Improved plan quality because during query compilation, the query optimizer considers using a filtered index in more situations	They are not so benificial in the query optimizer as Filtered Index
Online index rebuilds	A Filtered indexes while they are available for queries	Online index rebuilds are not supported for indexed views
Non-unique indexes	Filtered indexes can be non-unique	Indexed views must be unique
Only One Table	A Filtered Index is created on column(s) of a particular table.	Index Views can be created on column(s) from multiple base tables.
Simple WHERE criteria	A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause, NOT IN, OR and dynamic / non-deterministic predicates like WHERE col >= DATEADD(DAY, -1, GETDATE()) are not allowed, only simple comparison operators are allowed.	This limitation does not apply to indexed views and you can design your criteria as complex as you want.

Limitations and Restrictions

• Filtered index is very useful when a stored procedure must routinely select a specific type of result set from a large vertical (Entity-Attribute-Value) table. With a traditional non-filtered index, selecting the type of data one needs from an EAV tables can still be a slog.
• We cannot create a filtered index on a view. However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view.
• Filtered indexes have the following advantages over indexed views:
• A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.
• A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.
• A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.
• The clustered index key of the table does not need to be a key or included column in the filtered index definition. The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.
• If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

Type of Indexes in SQL

The following types of indexes are available in SQL Server-

1)  Clustered Index

2)  Nonclustered Index

3)  Unique Index

4)  ColumnStore Index

5)  Index with Included Columns Index

6)  Index on Computed Columns Index

7)  Filtered Index

8)  Spatial Index

9)  XML Index

10) Full-text Index
11) Indexed View
12) Partition Indexes
References: https://msdn.microsoft.com/en-us/library/cc280372.aspx