SQL 2008+

SQL Server Filtered Indexes

by Richard Carr, published at http://www.blackwasp.co.uk/SQLFilteredIndexes.aspx

SQL Server allows data in selected columns from entire tables to be indexed, in order to improve the performance of some queries. SQL Server 2008 introduces the concept of filtered indexes. These index a limited set of the rows in a table.

Previous: Page 1

Creating a Unique Filtered Index

As mentioned earlier, unique filtered indexes allow you to enforce uniqueness for all rows that meet specific criteria, whilst allowing duplication in other areas of the table. For the last example we'll create such an index. To begin, run the following script to create a table and populate it. The table holds product data. Each product has a unique number and a name that will always be set. Another column holds a supplier's product code. This must be unique but might not be known when the data is first input.

CREATE TABLE Products
(
    ProductID INT IDENTITY PRIMARY KEY,
    SupplierProductCode VARCHAR(20) NULL,
    Name VARCHAR(50)
)

INSERT INTO Products
    (SupplierProductCode, Name)
VALUES
    ('VAN-ICE', 'Vanilla Ice Cream'),
    ('CHC-ICE', 'Chocolate Ice Cream'),
    ('STR-ICE', 'Strawberry Ice Cream'),
    (NULL, 'Chocolate Sprinkles'),
    (NULL, 'Sugar Stars')

As the supplier product code should be unique, we might want to create a unique index for the column. However, because there can be multiple rows with a NULL value in this column, a standard index will not suffice.

Try running the following to create an index over all rows in the table.

CREATE UNIQUE NONCLUSTERED INDEX UQ_SupplierCodes
ON Products (SupplierProductCode)

The index cannot be created because the rows for Chocolate Sprinkles and Sugar Stars have the same SupplierProductCode value of NULL.

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for
the object name 'dbo.Products' and the index name 'UQ_SupplierCodes'.
The duplicate key value is (<NULL>).
The statement has been terminated.

With a filtered index we can overcome this problem. In the command below, the uniqueness is applied only for rows where SupplierProductCode is not NULL.

CREATE UNIQUE NONCLUSTERED INDEX UQ_SupplierCodes
ON Products (SupplierProductCode)
WHERE SupplierProductCode IS NOT NULL

This index is created successfully. To test that it's working, let's try to insert a new row with a duplicated, and therefore invalid, supplier product code:

INSERT INTO Products
    (SupplierProductCode, Name)
VALUES
    ('VAN-ICE', 'Vanilla Iced Sundae')

The above insert fails because of the constraint applied by the new index.

Cannot insert duplicate key row in object 'dbo.Products' with unique
index 'UQ_SupplierCodes'. The duplicate key value is (VAN-ICE).
The statement has been terminated.

You can still insert additional rows with no supplier product code. To show this, execute the following:

INSERT INTO Products
    (SupplierProductCode, Name)
VALUES
    (NULL, 'Vanilla Iced Sundae')

Finally, run the query below to show that all of the rows that should exist are present.

SELECT SupplierProductCode, Name FROM Products

/* RESULTS

SupplierProductCode Name
------------------- --------------------
VAN-ICE             Vanilla Ice Cream
CHC-ICE             Chocolate Ice Cream
STR-ICE             Strawberry Ice Cream
NULL                Chocolate Sprinkles
NULL                Sugar Stars
NULL                Vanilla Iced Sundae

*/

24 October 2013