Programming in MS SQL Server 2012 | ||||||||||||||||||
Filtered IndexA 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
|
* * * * *