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

40 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
  4. As indicated by Hawking, genuine man-made reasoning could realize the part of the arrangement race quicker than a worldwide temperature alteration or some other danger to humanity. artificial intelligence training in pune

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. And for making these decisions, managers need stats, trends and facts. Therefore, the importance of data science training can't be denied. artificial intelligence certification

    ReplyDelete
  7. Salesforce development also offers better response to the questions posted by the customers and businesses. Salesforce interview questions and answers

    ReplyDelete
  8. Thanks for sharing this valuable information and we collected some information from this post.

    Java Training in Chennai

    Java Course in Chennai

    ReplyDelete
  9. “Interesting  information, thanks for making these contributions.
    Great  information Glad to find your article.!This blog is really very informative.
    Thanks for sharing it with us
    bangalore escorts service 
     bangalore call girls 
    bangalore russian escorts 
    bangalore cheap escorts 
    bangalore escorts 

    ReplyDelete
  10. tools that automate and scale events personalize attendee experiences and deliver positive ROI. event marketing, quote planners and appreciation mails to team

    ReplyDelete
  11. หาคุณกำลังหาเกมส์ออนไลน์ที่สามารถสร้างรายได้ให้กับคุณ เรามีเกมส์แนะนำ เกมยิงปลา รูปแบบใหม่เล่นง่ายบนมือถือ คาสิโนออนไลน์ บนคอม เล่นได้ทุกอุปกรณ์รองรับทุกเครื่องมือ มีให้เลือกเล่นหลายเกมส์ เล่นได้ทั่วโลกเพราะนี้คือเกมส์ออนไลน์แบบใหม่ เกมยิงปลา

    ReplyDelete
  12. one fast silveOnline football betting Online casinos Baccarat on the internet In a handy manner, i99PRO 24 hour system can enable you to pleasantly bet on boxing. And as well boasts a full system Open just one single website, this can do everything. Since the heart desires actually There's additionally a Call Center personnel that are made twenty four several hours one day plus our website offers a wide range of solutions including web based football betting. Boxing betting online and casinos that are starting to be famous within this dimensions. And still well known as one of the top part. Our i99PRO website has also gained the confidence in addition to being total satisfaction of many gambling fanatics. And as well offers a wide range of football, boxing, basketball, lottery, web based casinos And there are a lot more different types of sports waiting for one to come in.r fox สล็อต

    ReplyDelete
  13. Study Amazon Web Services for making your career as a shining sun with Infycle Technologies. Infycle Technologies is the best AWS training institute in Chennai, providing complete hands-on practical training of professional specialists in the field. In addition to that, it also offers numerous programming language tutors in the software industry such as Oracle, Python, Big Dat, Hadoop, etc. Once after the training, interviews will be arranged for the candidates, so that, they can set their career without any struggle. Of all that, 200% placement assurance will be given here. To have the best career, call 7502633633 to Infycle Technologies and grab a free demo to know more.
    No.1 AWS Training Institute in Chennai | Infycle Technologies

    ReplyDelete
  14. That's good article! I really love this website for their fantastic contribution. let me now enjoy my pes 2022 iso ppsspp android

    ReplyDelete
  15. To ensure that we are the highest quality and most effective security company in London, all of our security team have a SIA Close Protection licence security chauffeur
    and they are required to undergo security screening and vetting in compliance with BS7858 standards and sign non-disclosure agreements as well as non-compete agreements.

    ReplyDelete
  16. Our girls believe in 100% satisfaction of our clients and make extra efforts to achieve it. Click Hot Girls

    ReplyDelete
  17. Looking for a place to spend time with great women while visiting your favorite city? Best place

    ReplyDelete
  18. Very good post here about new construction. I got very good knowledge here and I am happy to improve my knowledge. Thanks for it Click here

    ReplyDelete
  19. We are the top models agency in Mumbai with a great collection of elite and beautiful girls. Visit Naughty Girls

    ReplyDelete
  20. If you're looking for a friendly and outgoing Mumbai Models to join you at the hottest events around town, then look no further.

    ReplyDelete

  21. I am a new user of this site so here i saw multiple articles and posts posted by this site,I curious more interest in some of them hope you will give more information on this topics in your next articles.data science training in dehradun

    ReplyDelete
  22. Baby You Are My Teddy Bear. I just want to hold you everytime. LoveThisPic offers Baby You Are My Teddy Bear. I Just Want To Hold You Everytime pictures, photos.Teddy Bear Quotes

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete

Popular Posts