SQL SERVER – Introduction to Filtered Index – Improve performance with Filtered Index

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'

SQL SERVER - Introduction to Filtered Index - Improve performance with Filtered Index 1_Index

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.

SQL SERVER - Introduction to Filtered Index - Improve performance with Filtered Index 2_ExecutionPlan

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)

, ,
Previous Post
SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example
Next Post
SQL SERVER – 2008 – Hardware and Software Requirements for Installing SQL Server 2008

Related Posts

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:-)

    Reply
  • 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. :)

    Reply
  • Eliezer Figueroa
    July 17, 2009 9:21 pm

    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.

    Reply
  • 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

    Reply
    • 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.

      Reply
  • Hi,

    good explanation. i am trying to implement…..

    Reply
  • 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

    Reply
  • Excellent answer with good demonstration of eample.

    Reply
  • Venkata S Kumar
    March 7, 2011 9:47 am

    hi
    iam new to sql server .so i want best examples for coding purpose.plz send me website
    Regards
    Venkat

    Reply
  • Hi Pinal,

    Thanks for the article.

    Nice explanation for SQL Server 2008 new feature

    Reply
  • Hi Pinal,

    Thanks for the article.

    Reply
  • Hi Pinal,

    Keep up the good work. We all who work in sql server admire your work.

    Thanks and best regards,
    Eric Lall

    Reply
  • HI,

    Thanks for the wonderful article with the perfect example.

    Keep rocking:)

    Reply
  • 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.

    Reply
  • 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’.

    Reply
    • 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…….

      Reply
  • I’m working on SQL Server 2008 R2. FYR

    Reply
  • How many filtered Indexes can we create on a table? is it safe to have many filtered indexes on a table?

    Reply
  • Is it possible to add more data to filtered index without dropping and creating index again?

    Reply
  • 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?

    Reply
  • Sreenadha reddy
    March 1, 2013 1:32 pm

    Hi Pinal,

    Ur articles are really simple and superb to understand by starters and exp guys.
    Thanks lot a man, keep it up.

    Reply
  • sunil kr, jain
    March 8, 2013 8:40 am

    this is good example for understanding the filter index .

    Reply

Leave a Reply

Menu