Filtered index in SQL Server


Filtered Index were first introduced with SQL Server 2008. This article outlines what is the Filtered index, how it is different from other indexes, when to use the filtered index and how to construct a filtered index.

Filtered Index were first introduced with SQL Server 2008. A filtered index is constructed in the same way as other table index; however the filtered index includes a WHERE clause that restricts the rows to be included in the index. Filtered index are applied to a subset of rows within a table; thus they minimize the index storage requirement; also insert and update operations on rows excluded by filtered index will have no performance implications.

Example:
To illustrate the filtered index we need sample table and some data.


Create table Table1
(
ID INT PRIMARY KEY,
Col1 varchar(50),
Col2 INT NULL
)

Insert into Table1 (ID, Col1, Col2)
Values
(1, 'Item1', NULL),
(2, 'Item2', NULL),
(3, 'Item3', 1),
(4, 'Item4', 1),
(5, 'Item5', 2),
(6, 'Item6', 2),
(7, 'Item7', 6)


Filtered index uses almost the same syntax as for a standard non-clustered index, with the difference of WHERE clause
which determines the rows to be indexed. Rows for which WHERE predicates returns FALSE are excluded from index.


Create NONCLUSTERED INDEX IX_Table1
On Table1 (Col1)
Where Col2 IS NULL


This filtered index can be used by queries that limits to rows within the index. One such example
is given below.


Select * from Table1 where Col2 IS NULL and Col1 = 'Item2'


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: