Programming in MS SQL Server 2012

Filtered Index

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

When to Use Filtered Index

Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Examples are:

01. Sparse columns that contain only a few non-NULL values.
02. Heterogeneous columns that contain categories of data.
03. Columns that contain ranges of values such as dollar amounts, time, and dates.
04. Table partitions that are defined by simple comparison logic for column values.

The following example describes to creates a Filter Index with sample SELECT query.

-- Example 151 --

USE EBusiness

-- Create a new Product_Dimension Table --

CREATE TABLE Product_Dimension

(

Product_Key int Primary Key,

Product_Code int Not Null,

Product_Name varchar(50) Not Null,

Unit_Price numeric(7,2),

Start_Date datetime,

End_Date datetime

)

-- Find an existing index on IX_Product_Dimension and delete it if found --

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_Product_Dimension')

DROP INDEX IX_Product_Dimension on dbo.Product_Dimension

-- Create Filtered Index on Product_Dimension Table --

CREATE NONCLUSTERED INDEX IX_Product_Dimension

ON dbo.Product_Dimension (Product_Code, Start_Date)

WHERE End_Date IS NOT NULL

-- Sample SELECT Query --

SELECT Product_Key, Product_Code, Unit_Price, Start_Date

FROM dbo.Product_Dimension

WHERE End_Date IS NOT NULL

AND Product_Key = 27

AND Start_Date >= '12/31/2000'

Query Output Screen

Limitation of Filtered Index

Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In this case, you should use a full-table index instead of a filtered index.

Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Views vs. Filtered Indexes

A view is a virtual table that stores the definition of a query; it has a broader purpose and functionality than a filtered index. The following table compares some of the functionality allowed in views with that of filtered indexes.

Allowed in Expressions	Views	Filtered Indexes
Computed Columns	Yes	No
Joins	Yes	No
Multiple Tables	Yes	No
Simple comparison logic in predicate	Yes	Yes
Complex logic in predicate	Yes	No

* * * * *

Your Comment To AUTHOR