SQL 2008 Filtered Indexes

This sample illustrates the use of filtered indexes.

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

SELECT region_cd, region_name, region_phone
FROM Regions
WHERE region_cd = 'SE';

SELECT region_cd, region_name, region_phone
FROM Regions
WHERE region_cd = 'NE';

GO

SET SHOWPLAN_TEXT OFF;
GO

-- Guarantee unique values excuding NULLs
CREATE UNIQUE NONCLUSTERED INDEX ix_RegionPhone
ON Regions (region_phone)
WHERE region_phone IS NOT NULL;

-- OK

INSERT INTO Regions VALUES ('NW', 'North West', NULL);

-- Error:
-- Cannot insert duplicate key row in object 'dbo.Regions' with unique index...
INSERT INTO Regions VALUES ('NW', 'North West', '902-202-1234');

GO

SELECT * from Regions

DROP TABLE Regions;