MS SQL Server 2012 - DBA Articles

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

GO

 

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

)

GO

 

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

GO

 

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

GO

 

-- 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'

GO


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

* * * * *


Email Your Comment To AUTHOR