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]
                JobTitle= 'Marketing Manager'

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]
---- Create Filtered Index
JobTitle= 'Marketing Manager'
---- select data after creating filtered index
                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

/*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-

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


  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.

    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important. Project Center in Chennai It’s what organizations do with the data that matters. Big data can be analyzed for insights that lead to better decisions and strategic business moves.

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.
      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

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

  3. So, if i still got here and read first few paragraphs before realizing this is copy paste from MSDN(, 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)

  4. Really I Appreciate The Effort You Made To Share The Knowledge. This Is Really A Great Stuff For Sharing. Keep It Up . Thanks For Sharing.

    Mobile Apps Training
    Mobile Apps Training in Chennai

  5. Wow, Excellent post. This article is really very interesting and effective.The article you have shared here very awesome. I really like and appreciated your work. I read deeply your article, the points you have mentioned in this article are useful.

    iOS Training
    iOS Training in Chennai

  6. 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

  7. Replies
    1. This comment has been removed by a blog administrator.

  8. Appslure is Best app development company in mumbai and you can get website development service at a very affordable price.
    App development company in mumbai

  9. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

  10. Great blog !It is best institute.Top Training institute In chennai

  11. I am bookmarking your article so I can revisit and review more of your content. I agree with many of your thoughts and am impressed with your out-of-the box thinking.
    Best Data Science training in Mumbai

    Data Science training in Mumbai

  12. 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

  13. Awesome post with lots of data and I have bookmarked this page for my reference. Share more ideas frequently. oracle training in chennai

  14. Amazing and interesting blog found to be well written in a simple manner that everyone will understand and gain the enough knowledge from your blog being more informative is an added advantage for the users who are going through it. Once again nice blog keep it up.
    DevOps Training in Chennai

    DevOps Online Training in Chennai

    DevOps Training in Bangalore

    DevOps Training in Hyderabad

    DevOps Training in Coimbatore

    DevOps Online Training


  15. Firstly talking about the Blog it is providing the great information providing by you . Thanks for that .Hope More articles from you . Next i want to share some information about Salesforce training in Banglore .

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

  17. Excellent blog thanks for sharing the valuable becomes easy to read and easily understand the information.
    Useful article which was very helpful. also interesting and contains good information.
    to know about python training course , use the below link.

    Python Training in chennai

    Python Course in chennai

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

    Java Training in Chennai

    Java Course in Chennai

  19. Thanks for this. I really like what you've posted here and wish you the best of luck with this blog and thanks for sharing

    Teradata Training in Bangalore

  20. This Post is really supportive to all of us. Eager that these kind of information you post in future also.
    Teradata Training in Bangalore

  21. “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 

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

  23. อีกทั้งเรายังให้บริการ เกมสล็อต ยิงปลา แทงบอลออนไลน์ รองรับทุกการใช้งานในอุปกรณ์ต่าง ๆ HTML5 คอมพิวเตอร์ แท็บเล็ต สมาทโฟน คาสิโนออนไลน์ และมือถือทุกรุ่น เล่นได้ตลอด 24ชม. ไม่ต้อง Downloads เกมส์ให้ยุ่งยาก ด้วยระบบที่เสถียรที่สุดในประเทศไทย

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

  25. 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 สล็อต

  26. Worth reading! Our experts also have given detailed inputs about these trainings & courses! Presenting here for your reference. Do checkout Aws training in chennai & enjoy learning more about it.

  27. 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

  28. The engagement level within the content high you have got mentioned valuable points within the post. We wish to add a way higher in your analysis reach leading mobile app development company for reference. For more details, visit top mobile app development company or contact us: +91-9717270746 or email us:

  29. Grab the Oracle Certification in Chennai from Infycle Technologies, the best software training institute, and Placement center in Chennai which is providing professional software courses such as Data Science, Artificial Intelligence, Cyber Security, Big Data, Java, Hadoop, Selenium, Android, and iOS Development, DevOps, etc with 100% hands-on practical training. Dial 7502633633 to get more info and a free demo and to grab the certification for having a peak rise in your career. Grab Oracle Certification in Chennai | Infycle Technologies

  30. The article was absolutely fantastic! Lot of great information which can be helpful in some or the other way. Keep updating the blog, looking forward for more contents.
    by cognex is the AWS Training in Chennai

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


Popular Posts