Limitations of Filtered Indexes
The important thing to note is that you cannot create a Filtered Index on a View. However, you can define a filtered index on the underlying table used by the view instead of creating an index on a view. This will also help in reducing the index storage space and maintenance costs. The following conditions needs to be met when you want to leverage Filtered indexes when views are created. The first condition is that the view should be referring to only one table. Secondly, the queries should not be referring any computed columns, UDT columns, Spatial Data Type column, and HierarchyID Data Type column. Finally, Filtered Indexes cannot be used in XML and Full Text Indexes.
Example on Filtered Index in SQL Server 2008
In this example we will be using HumanResources.Employee table which is available in AdventureWorks database of SQL Server 2008. Let us first find out the list of indexes which are available on HumanResources.Employee table by running the below TSQL.
USE ADVENTUREWORKS
GO
— FIND NUMBER OF ROWS IN INDEXES
SELECT NAME, I.INDEX_ID, [ROWS]
FROM SYS.PARTITIONS P
INNER JOIN SYS.INDEXES I
ON P.[OBJECT_ID] = I.[OBJECT_ID]
AND P.INDEX_ID = I.INDEX_ID
WHERE P.[OBJECT_ID] = OBJECT_ID(‘HUMANRESOURCES.EMPLOYEE’)
GO
Now let’s find out the list of employees who are having title as ‘FINANCE MANAGER’
USE ADVENTUREWORKS
GO
SELECT HRE.EMPLOYEEID,HRE.LOGINID,HRE.TITLE
FROM HUMANRESOURCES.EMPLOYEE HRE
WHERE HRE.TITLE = ‘FINANCE MANAGER’
GO
You could see that in HUMANRESOURCES.EMPLOYEE table there is only one record which is having title as ‘FINANCE MANAGER’ out of 290 records. Now let us create a filtered index which will use the WHERE clause TITLE = ‘FINANCE MANAGER’ and check if there is any performance improvement by creating a Filtered Index. In the new query window press CTRL+M which will help you Include Actual Execution Plan and then run the below TSQL.
USE ADVENTUREWORKS
GO
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
— BEFORE CREATING A FILTERED INDEX
SELECT HRE.EMPLOYEEID,HRE.LOGINID,HRE.TITLE
FROM HUMANRESOURCES.EMPLOYEE HRE
WHERE HRE.TITLE = ‘FINANCE MANAGER’
GO
— CREATING A FILTERED INDEX
CREATE NONCLUSTERED INDEX FI_DEPARTMENT
ON HUMANRESOURCES.EMPLOYEE(EMPLOYEEID)
WHERE TITLE = ‘FINANCE MANAGER’
GO
— AFTER CREATING A FILTERED INDEX
SELECT HRE.EMPLOYEEID,HRE.LOGINID,HRE.TITLE
FROM HUMANRESOURCES.EMPLOYEE HRE
WHERE HRE.TITLE = ‘FINANCE MANAGER’
GO
