SQL Server Filtered Indexes

Summary: in this tutorial, you will learn how to use the SQL Server filtered indexes to create optimized non-clustered indexes for tables.

Introduction to SQL Server filtered indexes

A nonclustered index, when used properly, can greatly improve the performance of queries. However, the benefits of nonclustered indexes come at costs: storage and maintenance.

• First, it takes additional storage to store the copy of data of the index key columns.
• Second, when you insert, update, or delete rows from the table, SQL Server needs to update the associated non-clustered index.

It would be inefficient if applications just query a portion of rows of a table. This is why the filtered indexes come into play.

A filtered index is a nonclustered index with a predicate that allows you to specify which rows should be added to the index.

The following syntax illustrates how to create a filtered index:

CREATE INDEX index_name
ON table_name(column_list)
WHERE predicate;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the filtered index after the CREATE INDEX clause.
• Second, list the table name with a list of key columns that will be included in the index.
• Third, use a WHERE clause with a predicate to specify which rows of the table should be included in the index.

SQL Server filtered index example

We will use the sales.customers table from the sample database for the demonstration:

The sales.customers table has the phone column which contains many NULL values:

SELECT 
    SUM(CASE
            WHEN phone IS NULL
            THEN 1
            ELSE 0
        END) AS [Has Phone], 
    SUM(CASE
            WHEN phone IS NULL
            THEN 0
            ELSE 1
        END) AS [No Phone]
FROM 
    sales.customers;
Code language: SQL (Structured Query Language) (sql)

Has Phone   No Phone
----------- -----------
1267        178

(1 row affected)

This phone column is a good candidate for the filtered index.

This statement creates a filtered index for the phone column of the sales.customers table:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
WHERE phone IS NOT NULL;
Code language: SQL (Structured Query Language) (sql)

The following query finds the customer whose phone number is (281) 363-3309:

SELECT    
    first_name,
    last_name, 
    phone
FROM    
    sales.customers
WHERE phone = '(281) 363-3309';
Code language: SQL (Structured Query Language) (sql)

Here is the estimated execution plan:

The query optimizer can leverage the filtered index ix_cust_phone for searching.

Note that to improve the key lookup, you can use an index with included columns, which includes both first_name and last_name columns in the index:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
INCLUDE (first_name, last_name)
WHERE phone IS NOT NULL;
Code language: SQL (Structured Query Language) (sql)

Benefits of filtered indexes

As mentioned earlier, filtered indexes can help you save spaces especially when the index key columns are sparse. Sparse columns are the ones that have many NULL values.

In addition, filtered indexes reduce the maintenance cost because only a portion of data rows, not all, needs to be updated when the data in the associated table changes.

In this tutorial, you have learned how to use the SQL Server filtered indexes to create optimized nonclustered indexes for tables.