Filtered Index feature has been introduced since SQL Server 2008. In my recent survey with some Data Analysts, Database Developers and DBAs, it seems that Filtered Index has been ignored or forgotten. So here’s some reminder on why it’s a good idea to rethink about using Filtered Index.
Filtered index characteristics:
- an optimised nonclustered index
- containing only the specified subset of data, specified by the filter predicate
- referring to column(s) of the table which it belongs to
Having Filtered Index is particularly useful if only the same subset of data is being queried very frequently (or almost all the time). Filtered index will be smaller in size compared to an index with the same definition without filtered index.
Below is an illustration of the Production.WorkOrder set and its subset data where DueDate between 1 Jan 2011 and 30 Jun 2011.
Set Diagram representing Production.WorkOrder records
The Non-Filtered index size is much larger in comparison to the Filtered index size as shown below. The smaller the index size, the quicker for SQL Server will fetch the data.
Non Filtered Index vs Filtered Index Size Comparison
A couple of basic usage examples are listed below:
1. Reports are generated only for the year to date data.
A good example will be creating an index filtering the date column from the beginning of the year. e.g.
CREATE NONCLUSTERED INDEX IX_Production_WorkOrder_DueDate ON Production.WorkOrder
(
DueDate
)
WHERE DueDate >= '01 Jan 2011'
Of course, at the beginning of the year next year, you may wish to change the DueDate clause to refer to “01 Jan 2012” (instead of “01 Jan 2011”). To avoid manually changing the filter every year, you could create a SQL Server job that dynamically alters the index so that the filtered index is referring to the new DueDate filter at the specified date of the year.
2. Data is processed only for Customers that have email address ID value.
If there is more data with Non Nullable EmailAddressID values than the NULL EmailAddressID, creating filtered index will reduce size and therefore improve on performance (compared to creating a non-filtered index).
CREATE NONCLUSTERED INDEX IX_Person_Person ON Person.Person
(
EmailAddressID
)
WHERE EmailAddressID IS NOT NULL
More scenarios and best practice have been documented here on BOL: http://msdn.microsoft.com/en-us/library/cc280372(v=SQL.100).aspx
The filter predicate only supports simple comparison operator such as the following logical operators:
-
=
-
!=
-
<
-
<=
-
>
-
>=
-
IS NULL
-
IS NOT NULL
This means “LIKE” and “BETWEEN … AND …” comparisons cannot be used. However, there is a workaround to the “BETWEEN … AND …” comparison, i.e. by replacing it with the “>=” and “<=” operators, such as shown in the snippet below.
CREATE NONCLUSTERED INDEX IX_Production_WorkOrder_DueDate ON Production.WorkOrder
(
DueDate
)
WHERE DueDate >= '01 Jan 2011' AND DueDate <= '30 Jun 2011'
-- equivalent to DueDate BETWEEN '01 Jan 2011' AND '30 Jun 2011
When implementing Filtered Indexes, I would recommend running the result of turning on Statistics IO for before and after. This will give you a better understanding of how much improvement you will gain with the Filtered Index.
An article from MSSQLTips worth reading to get to know more about Filtered Index can be found here: http://www.mssqltips.com/sqlservertip/1785/sql-server-filtered-indexes-what-they-are-how-to-use-and-performance-advantages/
8 Responses
Is it possible to add more data to filtered index without dropping and re-creating it?
Yes, it is. You can add data to the table without dropping and re-creating filtered index. More information about filtered index is located here: http://technet.microsoft.com/en-us/library/cc280372.aspx
Hope this helps.
Julie
Thanks for very nice article. It helped me a lot…
Can i pass sub query in where condition like below…
CREATE NONCLUSTERED INDEX IX_ID ON Table.Employee
(
EmployeeID
)
WHERE EmployeeID in (Select Distinct EmployeeID from Table2)
Hi Neeraj,
The WHERE clause expression needs to be scalar. For example:
CREATE NONCLUSTERED INDEX ix_test ON dbo.Employee
(
EmployeeID
)
WHERE City = 'Los Angeles'
As per MSDN (http://msdn.microsoft.com/en-us/library/ms188783.aspx) under the “WHERE” section of the Arguments heading:
“The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.)”
Hope this helps!
Julie
Mam,i want to provide association between filtered-index column to another table column
like:
table_1(name,mail_id,address) –here mail_id is the filtered index column and table_2(mial_id ,……………some columns) — here in table_2 i want to provide foreign key to That table_1 of Mail_id Column) actually There is no use of this but i want to know is this allowing Fk or not
Hi Saida,
Apologies for the delayed reply. It’s been a hectic few weeks.
Filtered index does not enforce association between two tables. The ideal way of enforcing that is via a Foreign Key constraint (http://technet.microsoft.com/en-us/library/ms189049.aspx).
Thank you for stopping by.
Hope this helps,
Julie
very informative and helpful
thank u very much for superb article