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.

 

*/

Labels: ,