Filtered Indexes

Filtered indexes are another welcome addition in SQL Server 2008. They allow creating an index filtered with WHERE clause. While this was doable in SQL Server 2005 using indexed views on the table, the natural approach of directly defining a filtered index is much more appealing.

Below are some examples of using filtered indexes. The first one demonstrate how filtered index will assist on commonly searched values allowing to use INDEX SEEK for those, and a TABLE SCAN for less frequently searched values. The other example implements a very common request to define unique values for column by allowing multiple NULL values (using UNIQUE index allows only a single NULL value).

CREATE TABLE Regions (
 region_cd CHAR(2),
 region_name VARCHAR(35),
 region_phone VARCHAR(12) NULL);
 
INSERT INTO Regions VALUES ('NE', 'North East', NULL),
                           ('SW', 'South West', NULL),
                           ('SE', 'South East', '902-202-1234');
 
-- Index to filter on frequently queried values
CREATE NONCLUSTERED INDEX ix_SouthEastRegion
  ON Regions (region_cd)
  INCLUDE(region_name, region_phone)
  WHERE region_cd = 'SE';
 
GO
SET SHOWPLAN_TEXT ON;
GO
 
-- Typical user query
SELECT region_cd, region_name, region_phone
FROM Regions
WHERE region_cd = 'SE';
 
/*
 
-- Execution plan
StmtText
--------------------------------------------------------------
  |--Index Seek(OBJECT:([Testing].[dbo].[Regions].[ix_SouthEastRegion]), 
           SEEK:([Testing].[dbo].[Regions].[region_cd]='SE') ORDERED FORWARD)
 
*/
 
-- Less frequent user query
SELECT region_cd, region_name, region_phone
FROM Regions
WHERE region_cd = 'NE';
 
/*
 
-- Execution plan
StmtText
--------------------------------------------------------------
  |--Table Scan(OBJECT:([Testing].[dbo].[Regions]), 
           WHERE:([Testing].[dbo].[Regions].[region_cd]='NE'))
 
*/
 
GO
SET SHOWPLAN_TEXT OFF;
GO
 
-- Guarantee unique values excluding NULLs
CREATE UNIQUE NONCLUSTERED INDEX ix_RegionPhone
  ON Regions (region_phone)
  WHERE region_phone IS NOT NULL;
 
-- OK, multiple NULLs allowed because filtered out from the UNIQUE index
INSERT INTO Regions VALUES ('NW', 'North West', NULL);  
 
-- Attempt to insert duplicate non NULL value
INSERT INTO Regions VALUES ('NW', 'North West', '902-202-1234');  
 
/*
 
-- Error
Msg 2601, Level 14, State 1, Line 11
Cannot insert duplicate key row in object 'dbo.Regions' with unique index 'ix_RegionPhone'.
The statement has been terminated.
 
*/