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

Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

When we see an Index created with some WHERE clause then that is actually a FILTERED INDEX.

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 Filtered Index:

-          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 simple SELECT statement on the table where we created Filtered Index.

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 first case the index scan in 100% done on 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 table with no index.

If we have table with thousands of records and we are only concern with very few rows in our query we should use Filtered Index.

Conclusion:

A filtered Index is an optimized non clustered Index which is one of the great performance improvements in SQL SERVER 2008 reducing the Index storage cost and reduces maintenance cost.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – 2008 – Introduction to Filtered Index – Improve performance with Filtered Index

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

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

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

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

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

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

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

  8. 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?

  9. Hi Pinal,

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

  10. hi,
    i am new to sql server. i need some best coding examples for practice. Also need your suggestions for become good in sql server.
    waiting for your reply.
    [email removed]

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  12. One of the problem i faced by applying Filtered Indexes, when I create a agent job that executed TSQL Script (update etc), job fails to run and could not update the table. When I removed the F-Index, script worked perfectly fine.
    Amy advice.
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s