Filtered Index is a new feature in SQL SERVER. Filtered Index is used to index a portion of rows in a table that means it applies a filter on INDEX which improves query performance, reduces index maintenance costs, and reduce index storage costs compared with full-table indexes.
When we see an Index created with somewhere clause then that is actually a FILTERED.
For Example,
If we want to get the Employees whose Title is “Marketing Manager”, for that let’s create an INDEX on EmployeeID whose Title is “Marketing Manager” and then write the SQL Statement to retrieve Employees who are “Marketing Manager”.
CREATE NONCLUSTERED INDEX NCI_Department ON HumanResources.Employee(EmployeeID) WHERE Title= 'Marketing Manager'
Points to remember when creating a Filtered Ix:
– They can be created only as Nonclustered Index
– They can be used on Views only if they are persisted views.
– They cannot be created on full-text Indexes.
Let us write a simple SELECT statement on the table where we created a Filtered Ix.
SELECT he.EmployeeID,he.LoginID,he.Title FROM HumanResources.Employee he WHERE he.Title = 'Marketing Manager'
Now we will see the Execution Plan and compare the performance before and after the Filtered Index was created on Employee table.
As we can see, in the first case the index scan in 100% done on the Clustered Index taking 24% of total cost of execution. Once the Filtered index was created on Employee table, the Index scan is 50% on Clustered Index and 50% on Nonclustered Index which retrieves the data faster taking 20% of total cost of execution compared to 24% on the table with no index.
If we have a table with thousands of records and we are only concern with very few rows in our query we should use Filtered Ix.
Conclusion:
A Filtered Index is an optimized non clustered Index which is one of the great performance improvements in SQL SERVER reducing the Index storage cost and reduces maintenance cost.
Reference : Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
A major reason I would want to do this on a table or view is to allow the full text service to use filtered indexes based on language. As I work with multilingual databases, I need to create either separate language specific tables for the same data or use views (which is how I currently do it).
It seems filtered views have too many gotchas for me in this situation:-)
In this particular instance, if you will be sharing so many titles amongst your employees, you need to normalise your data structure and represent the Title in a separate table, with a FK reference the the Employee table. :)
Sometime you cant normalize be cause is a “closed” system but you are required to optimize. This is an administration feature not a developer feature.
Hi,
how many filtered index can we create on a table? we may want to execute a query with a few where condition and what do we have to do in this situation? and the other question is that if we want to execute this query for a non-specified where condition like Title=’Marketing’, do we have to write condition as Title=’%%’ ?? this article is very efficient but i thought theese questions after i read it.good works.thanks
Yes, you can use LIKE in any case.
But…
I don’t understand what you mean with “a non-specified condition.”
As I said before, you can use LIKE and if you still want to cover all the data you always have the normal indexes as you use to use until now without any filter at all.
Hi,
good explanation. i am trying to implement…..
Hi I’m new in the filtered index,I have table with millions of records and I belive that filtered index can help me,I try to use a field (indexed with filtered index) in a inner join but the performance it’s the same
create table invoiceDetail (status int)
create index dbo.FilteredIndex ON invoiceDetail(status) where status in (1,2,4)
–invoiceDetail table has a million of records
So, I have a temporal table with the name filterJoin with one field (filterValue) and two values (1,2)
I do the next query
select *
from invoiceDetail I
inner join filterJoin F On I.status and F.filterValue
the query is too slow
but when i try with
select *
from invoiceDetail I
where status in (1,2)
it’s to fast
I try
Hi I’m new in the filtered index,I have table with millions of records and I belive that filtered index can help me,I try to use a field (indexed with filtered index) in a inner join but the performance it’s the same
create table invoiceDetail (status int)
create index dbo.FilteredIndex ON invoiceDetail(status) where status in (1,2,4)
–invoiceDetail table has a million of records
So, I have a temporal table with the name filterJoin with one field (filterValue) and two values (1,2)
I do the next query
select *
from invoiceDetail I
where I.status in (select * from filterJoin)
it’s too slow and I need filter dinamically
Excellent answer with good demonstration of eample.
hi
iam new to sql server .so i want best examples for coding purpose.plz send me website
Regards
Venkat
Hi Pinal,
Thanks for the article.
Nice explanation for SQL Server 2008 new feature
Hi Pinal,
Thanks for the article.
Hi Pinal,
Keep up the good work. We all who work in sql server admire your work.
Thanks and best regards,
Eric Lall
HI,
Thanks for the wonderful article with the perfect example.
Keep rocking:)
This type of index is very useful when a stored procedure must routinely select a specific type of resultset 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.
Hi ,
This might be a very simple question but however just wanted to ask as I’m kind off stuck at a point. I’m trying to create a filtered index on a column, but I’m not able to because I’m getting a syntax error. I checked if this is permission issue, but that’s not the case, because I’m able to create and alter indexes and also use other option in the create index syntax.
CREATE NONCLUSTERED INDEX “IX_test_NC” ON
dbo.Table_name (column_name)
WHERE substring(column_name,1,3) = ‘value’
The error is :
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘WHERE’.
Hi AVK,
I am not very sure but I think we can not create the filtered indexes on the columns used inside the any scalar function.
Try to create an computed column index instead…….
I’m working on SQL Server 2008 R2. FYR
How many filtered Indexes can we create on a table? is it safe to have many filtered indexes on a table?
Is it possible to add more data to filtered index without dropping and creating index again?
Yes, ofcourse we can
we apply a filteredindex on a where clause it is applicable for a single record only it is some whats better than normal table ,why its fits best performance?
if apply either clustered or non clustred then these will reflected to total table then it fits the great performance kk
IS IT CURRECT OR NOT?
Hi Pinal,
Ur articles are really simple and superb to understand by starters and exp guys.
Thanks lot a man, keep it up.
this is good example for understanding the filter index .