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.
Difference between Filtered Index and Indexed View
Type of Indexes in SQL
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'
|
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-
3) Unique Index
9) XML Index
10) Full-text Index
11) Indexed View
12) Partition Indexes
References: https://msdn.microsoft.com/en-us/library/cc280372.aspx
11) Indexed View
12) Partition Indexes
References: https://msdn.microsoft.com/en-us/library/cc280372.aspx
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.
ReplyDeleteHi , i am looking of rJob opening on MSBI, if you have come across any MSBI Opporunities , please share to my emailid narayana2k6@gmail.com
ReplyDeleteThanks
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:
ReplyDelete1 - 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)
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.
ReplyDeleteMobile Apps Training
Mobile Apps Training in Chennai
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.
ReplyDeleteiOS Training
iOS Training in Chennai
Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way. T hank you so much.
ReplyDeleteBig Data Analytics Courses in Chennai
Big Data Analytics Training in Chennai
PHP Training in Chennai
Web Designing Course in chennai
Hadoop Training in Chennai
Big Data Analytics Training in OMR
Big Data Analytics Training in TNagar
Excellent Blog!!! Such an interesting blog with clear vision, this will definitely help many to make them update.
ReplyDeleteSpoken English Classes in Chennai
Best Spoken English Classes in Chennai
Spoken English Class in Chennai
Spoken English in Chennai
English Classes in Chennai
Nice blog!! I hope you will share more info like this. I will use this for my studies and research.
ReplyDeleteAngularjs Training in Chennai
Angularjs Course in Chennai
Web Designing Course in Chennai
PHP Training in Chennai
Angularjs Courses in Chennai
Angular Training in Chennai
Best Angularjs Training in Chennai
gst training in chennai
Angularjs Training in Chennai
Angularjs Course in Chennai
Thank you so much for providing information on this. It was very useful.
ReplyDeleteAviation Academy in Chennai
Air hostess training in Chennai
Airport management courses in Chennai
Ground staff training in Chennai
Aviation Courses in Chennai
air hostess course in Chennai
airport courses in Chennai
Ground staff training in Chennai
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
ReplyDeleteThanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
ReplyDeleteweb designer courses in chennai | best institute for web designing Classes in Chennai
mobile application development course | mobile app development training | mobile application development training online
web designing classes in chennai | web designing training institute in chennai
Web Designing Institute in Chennai | Web Designing Training in Chennai
website design course | Web designing course in Chennai
Thanks for posting this information. Keep updating.
ReplyDeletepearson vue test center in chennai
French Language Classes in Chennai
Informatica MDM Training in Chennai
Hadoop Admin Training in Chennai
Blockchain Training in Chennai
Data Analytics Courses in Chennai
Informatica Training Center Chennai
spanish language in chennai
content writing training in chennai
Washing Powder Pcakaging
ReplyDeleteWashing Powder Pcakaging bags
Detergent packaging pouch
Lyrics with music
we have provide the best ppc service.
ReplyDeleteppc company in gurgaon
website designing company in Gurgaon
PPC company in Noida
seo company in gurgaon
PPC company in Mumbai
PPC company in Chandigarh
Digital Marketing Company
iso certification in noida
ReplyDeleteiso certification in delhi
ce certification in delhi
iso 14001 certification in delhi
iso 22000 certification cost
iso consultants in noida
iso 9001 certification in Delhi
ReplyDeleteiso 27001 certification services
ISO 9001 Certification in Noida
iso 22000 certification in Delhi
website designing services
SEO Service Consultant
Appslure is Best app development company in mumbai and you can get website development service at a very affordable price.
ReplyDeleteApp development company in mumbai