SQL Server Filtered Indexes: When They Help and When They Do Not

Introduction

Have you ever worked with a SQL Server table that contains millions of records, but your application regularly queries only a small portion of that data?

For example, an order management system may store every order created over several years. However, the application's dashboard may display only orders that are still pending.

Creating an index on the entire table can help, but it may also increase storage requirements and the cost of maintaining the index when records are inserted or updated.

SQL Server filtered indexes offer another option. They allow you to create an index that contains only the rows matching a specific filter condition.

Instead of indexing every order, you could create an index that contains only pending orders. This can reduce the size of the index and help SQL Server find the relevant records more efficiently.

However, filtered indexes are not suitable for every query. Their effectiveness depends on the filter condition, query structure, data distribution, and execution plan.

In this article, we will learn how filtered indexes work, how to create them, when they improve performance, and what to check when SQL Server does not use them.

What Is a Filtered Index in SQL Server?

A filtered index is a nonclustered index that contains only the rows satisfying a specified filter condition.

Consider an Orders table with the following columns:

• OrderId
• CustomerId
• OrderDate
• Status
• TotalAmount

Suppose the table contains several million orders, but the application frequently retrieves orders with a status of Pending.

A regular index may contain entries for orders with every status. A filtered index can contain entries only for pending orders.

Here is an example:

CREATE NONCLUSTERED INDEX IX_Orders_Pending
ON dbo.Orders (OrderDate)
INCLUDE (CustomerId, TotalAmount)
WHERE Status = 'Pending';

This index contains entries only for rows where Status is Pending.

The index key is OrderDate, while CustomerId and TotalAmount are included as non-key columns.

This design can help queries that filter by pending status and need the indexed columns. SQL Server may be able to retrieve the required information directly from the index without reading the underlying table or clustered index for every matching row.

The actual benefit depends on the query and the execution plan.

How Does a Filtered Index Work?

Imagine an order table containing one million records:

These numbers are illustrative.

If the application regularly queries pending orders, an index containing only those 20,000 records may be considerably smaller than an index covering all one million orders.

When SQL Server can use the filtered index, it has fewer index entries to search and maintain than it would with an equivalent full-table index.

However, a filtered index does not automatically make every query faster. SQL Server must determine that the index can satisfy the query and that using it is a good choice based on the estimated cost.

Filtered Index vs. Regular Index

Both regular and filtered indexes can improve query performance, but they serve different needs.

A filtered index is not always better than a regular index. If most rows match the filter, the size advantage may be small.

When Should You Use a Filtered Index?

Filtered indexes are particularly useful when your application repeatedly queries a well-defined subset of a table.

1. Index pending orders

Suppose your application needs to display pending orders, sorted by creation date.

CREATE NONCLUSTERED INDEX IX_Orders_Pending
ON dbo.Orders (OrderDate)
INCLUDE (CustomerId, TotalAmount)
WHERE Status = 'Pending';

The query might look like this:

SELECT
    OrderDate,
    CustomerId,
    TotalAmount
FROM dbo.Orders
WHERE Status = 'Pending'
ORDER BY OrderDate;

The query predicate matches the filtered index condition. SQL Server can consider the index for filtering and ordering, and the included columns may allow it to return the requested data without additional lookups.

This is a good candidate for a filtered index if pending orders represent a relatively small portion of the table and the query runs frequently.

2. Index records that have not been processed

Consider a background processing system that regularly checks for records that have not yet been processed.

CREATE NONCLUSTERED INDEX IX_Jobs_NotProcessed
ON dbo.Jobs (CreatedAt)
INCLUDE (JobType)
WHERE IsProcessed = 0;

The application can use a query such as:

SELECT
    CreatedAt,
    JobType
FROM dbo.Jobs
WHERE IsProcessed = 0
ORDER BY CreatedAt;

If only a small percentage of jobs remain unprocessed, the filtered index may reduce the amount of index data SQL Server needs to examine.

As jobs are processed and IsProcessed changes to 1, their entries no longer belong in the filtered index.

This makes filtered indexes useful for certain queue-like tables, although the overall design must still account for concurrent workers and how jobs are claimed.

3. Index active records

Some applications keep inactive records for auditing but frequently retrieve only active ones.

CREATE NONCLUSTERED INDEX IX_Customers_Active
ON dbo.Customers (LastName, FirstName)
INCLUDE (Email)
WHERE IsActive = 1;

This index may help when the application searches or sorts active customers by name.

Before creating it, check whether the application also performs queries that need inactive customers. Those queries may require a different index or access path.

How to Check Whether SQL Server Uses the Filtered Index

Creating an index does not guarantee that SQL Server will use it.

First, enable the actual execution plan in SQL Server Management Studio. Then run the query you want to optimize.

SELECT
    OrderDate,
    CustomerId,
    TotalAmount
FROM dbo.Orders
WHERE Status = 'Pending'
ORDER BY OrderDate;

Review the execution plan for an index seek or scan using IX_Orders_Pending.

An index seek can be efficient when SQL Server needs only a small range of entries. An index scan can also be appropriate if many entries need to be read.

Do not judge performance by the operator name alone. Compare the number of rows read, logical reads, execution time, and any additional lookups.

You can also collect basic I/O and timing information:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT
    OrderDate,
    CustomerId,
    TotalAmount
FROM dbo.Orders
WHERE Status = 'Pending'
ORDER BY OrderDate;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Run the query with representative parameters and data. Compare the results before and after creating the index, and account for caching and workload variation.

When Filtered Indexes Do Not Help

Filtered indexes are useful, but there are situations where they provide little benefit or are not eligible for a particular query.

The filter matches most of the table

If 95 percent of the records satisfy the filter, the index may still contain most of the table's rows.

A regular index may be more appropriate, depending on the query and the columns involved.

The query does not imply the filter condition

Suppose you create this index:

CREATE NONCLUSTERED INDEX IX_Orders_Pending
ON dbo.Orders (OrderDate)
WHERE Status = 'Pending';

A query that retrieves all orders cannot use this index alone to return the complete result because the index excludes orders with other statuses.

SQL Server must be able to prove that the filtered index contains all rows required by the query.

The query uses a parameterized predicate

Parameterized queries are generally good for security and plan reuse, but they can complicate filtered-index selection.

For example:

SELECT
    OrderDate,
    CustomerId,
    TotalAmount
FROM dbo.Orders
WHERE Status = @Status;

If @Status can contain different values, SQL Server may need a plan that works for values other than Pending. Depending on parameterization and plan compilation, the filtered index may not be eligible or selected.

Do not solve this by automatically concatenating user input into dynamic SQL. First inspect the execution plan and determine whether the query can safely be structured to make the filter condition clear.

Best Practices for Filtered Indexes

1. Choose a filter based on real query patterns

Use query logs, Query Store, or workload analysis to identify frequently executed queries that access a specific subset of records.

Avoid creating filtered indexes simply because a column contains values such as Active, Pending, or IsDeleted.

2. Keep the index as narrow as possible

Choose key columns that support filtering, sorting, or joins. Use included columns for additional values needed by the query.

Adding unnecessary columns increases storage and maintenance costs.

3. Check the execution plan before and after

Verify that SQL Server uses the index appropriately and that the change improves the workload. Compare logical reads and execution time rather than assuming a smaller index is always faster.

4. Consider data changes

Filtered indexes are maintained when changes cause rows to enter or leave the filtered set.

For example, changing an order from Pending to Completed removes its entry from the pending index. If your application changes status frequently, include that maintenance cost in your evaluation.

5. Maintain appropriate statistics

SQL Server maintains statistics for indexes, including filtered indexes. Good statistics help the optimizer estimate the number of matching rows.

Investigate stale or inaccurate estimates when the execution plan does not match the actual workload.

Common Mistakes and Troubleshooting

One common mistake is creating several filtered indexes for every possible status. Each additional index consumes storage and adds maintenance work when relevant records change.

Start with the indexes that support important queries, and remove those that do not provide enough benefit.

Advantages and Disadvantages of Filtered Indexes

Advantages

• Can reduce index size when only a small subset of rows is relevant.
• May improve queries that repeatedly access a specific subset of data.
• Can reduce storage and maintenance compared with an equivalent full-table index.
• Can provide useful filtered statistics for the optimizer.
• May avoid extra lookups when the index covers the query.

Disadvantages

• Only applies to queries that can use the filtered subset.
• Offers limited size benefits when most rows match the filter.
• Adds maintenance overhead when rows enter or leave the filtered set.
• May not be selected for some parameterized queries.
• Requires careful testing as data distribution and workload change.

Summary

SQL Server filtered indexes are useful when your application regularly queries a small, well-defined portion of a large table.

They can help with scenarios such as pending orders, unprocessed jobs, and active customer records. By indexing only the relevant rows, you may reduce index size and improve query performance.

However, a filtered index is not a universal replacement for a regular index. Its value depends on the filter condition, the query structure, data distribution, and the work SQL Server must perform.

Before adding one, identify a real performance problem, inspect the execution plan, and compare logical reads and execution time. Then monitor the index as your data and workload evolve.

The best filtered index is one that supports a real query pattern and provides measurable benefits without adding unnecessary maintenance cost.