Thursday, July 9, 2015

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 nonclustered 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-
10) Full-text Index
11) Indexed View
12) Partition Indexes
References: https://msdn.microsoft.com/en-us/library/cc280372.aspx

3 comments:

  1. I recommend my clients think (and TEST) VERY hard before implementing filtered indexes. They are riddled with bugs and issues and query optimizer gaps. Search for SQL Server filtered index (problems OR bug) and you will get a LOT of useful references about their many issues. They COULD have been a great feature, but they were released too early and never received the attention they needed from the product team to fulfill their potential.

    ReplyDelete
  2. Hi , i am looking of rJob opening on MSBI, if you have come across any MSBI Opporunities , please share to my emailid narayana2k6@gmail.com
    Thanks

    ReplyDelete
  3. So, if i still got here and read first few paragraphs before realizing this is copy paste from MSDN(https://msdn.microsoft.com/en-us/library/cc280372.aspx), I can add some topics on which you can investigate and maybe add to your article:
    1 - talk about ANSI restrictions on filtered indexes and how you can break you application in less than 1 minute if you don't consider them
    2 - talk about and add examples on what downsides a filtered index has when you're updating data (INSERT/UPDATE/DELETE)

    ReplyDelete

Popular Posts