In SQL Server, a filtered index is a special type of index that only includes a subset of rows from a table or view, based on specified filter criteria.

This criteria would match the criteria of some of the most commonly run queries on the table, thereby helping to improve the performance of those queries.

Below is an example of creating a filtered index in SQL Server.

Example

The criteria for a filtered index can be specified with a WHERE clause, just like when filtering query results from a SELECT statement.

Here’s an example:

CREATE INDEX idx_current_tasks ON Tasks (TaskId, StartDate)
WHERE EndDate IS NULL;

This creates a filtered index called idx_current_tasks, which is applied to the TaskId and StartDate columns of the Tasks table.

The important thing here is that the index filters out all rows that have a NULL value in the EndDate column.

This index could be useful for the following query:

SELECT TaskName, TaskID, StartDate
FROM Tasks
WHERE EndDate IS NULL
    AND TasktID = 102
    AND StartDate > '01/01/2024';

So because the query includes EndDate IS NULL, our index can be useful, because it has already filtered out those rows. So it should be a lot quicker than had we put a full index on the column.

The index will be particularly beneficial if the number of rows that contain NULL are relatively small compared to the number of rows in the table. That’s because the index won’t need to include all of the irrelevant rows in the index. When we run a query that uses the filtered index, it will only need to filter through the small number of rows defined by our filtered index.

Limitations

It’s important to note that we can only use simple comparison operators when creating a filtered index, we can’t use the LIKE operator, and we can’t create a filtered index on a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column.

There are also other limitations, outlined by Microsoft’s documentation here.

Introduction to Indexes

See my Introduction to Indexing in SQL for an overview of what indexes are in SQL and why they can be useful for speeding up slow-running queries.