When active rows are a small slice of a table, filtered indexes can keep searches narrow. They are especially useful when most rows are historical, inactive, or missing a value. The trick is to write a filter the optimizer can prove matches the query.
What a Filtered Index Stores
A filtered nonclustered index contains entries only for rows that satisfy its WHERE clause. The index can be smaller than a full-table index, with fewer pages to read and less storage to maintain. Statistics attached to the filtered index describe that subset, which can help estimates for matching queries. A filter is part of index design, not a runtime shortcut.
If only a small portion of rows meet the condition, the benefit can be substantial. If most rows qualify, the filtered index begins to resemble a regular index with additional eligibility rules. I start with the data distribution and the exact query predicate before creating one. A fashionable filter is still an index that every qualifying write must maintain. Does your most important query state the filter in a way the optimizer can prove?
Choose Stable, Selective Predicates for Filtered Indexes
Soft-delete flags, open orders, active subscriptions, and non-NULL sparse columns are common candidates. The filter should represent a meaningful subset that important queries read repeatedly. Its definition must use supported simple comparison logic. Avoid designing a filter around a volatile state if rows will constantly move in and out of it.
A status field can look selective today and become broad after a business change. Measure counts by status and track their movement. If half the table is active, a filtered index can still help, but the benefit needs evidence. I prefer a filter that aligns with a stable business question, such as currently open work, instead of a number chosen for a demonstration.
Create an Active-Row Index
Suppose dbo.Tickets holds years of closed tickets and a modest active set. This index carries only active rows, keyed by customer and creation time, and includes a value the application displays. The query repeats the filter explicitly. Verify that the data types and comparison value match the actual table design.
The index helps only if the optimizer can establish that the query returns rows inside its filter. A different status predicate or a broad query over all tickets needs another access path. After creation, compare actual plans and logical reads for representative customers.
CREATE INDEX IX_Tickets_Active_Customer_Created
ON dbo.Tickets (CustomerID, CreatedAt)
INCLUDE (Priority)
WHERE Status = 'Active';
SELECT TicketID, CreatedAt, Priority
FROM dbo.Tickets
WHERE CustomerID = 42
AND Status = 'Active'
ORDER BY CreatedAt DESC;Handle Sparse Values
A mostly NULL column is another useful pattern. If a processing system searches only rows with an external identifier, indexing every NULL row can waste space. A filtered index on ExternalID IS NOT NULL keeps the useful subset. Check that the query also includes the non-NULL condition or a predicate that clearly implies it.
Sparse data does not guarantee a win. If the non-NULL subset is huge or the query needs many other columns, a scan or a different index can be cheaper. I inspect the actual plan and row counts, then compare index size and write overhead. The benefit should survive realistic parameter values, not only a hand-picked lookup.
CREATE INDEX IX_Orders_ExternalID
ON dbo.Orders (ExternalID)
WHERE ExternalID IS NOT NULL;
SELECT OrderID, ExternalID
FROM dbo.Orders
WHERE ExternalID = N'EXT-1042'
AND ExternalID IS NOT NULL;
Understand the Parameter Problem with Filtered Indexes
A parameterized query such as WHERE Status = @Status can receive ‘Active’ or ‘Closed’. A cached plan must remain correct for both values, so the optimizer cannot assume every execution belongs inside a filter on Status = ‘Active’. It can choose a broader index even when the current parameter is ‘Active’. That is a correctness constraint, not a refusal to use the index.
A dedicated active-row query with a literal condition can give the optimizer proof. OPTION (RECOMPILE) can also let an execution use its current parameter value, at a compilation cost. Dynamic SQL has its own operational and security requirements. Choose an approach only after measuring the workload and preserving parameter safety.
Check Filter and Query Equivalence
The query predicate must imply the filter. A query for Status = ‘Active’ fits the active-row index. A query for Status IN (‘Active’, ‘Pending’) does not fit because it can return rows absent from the index. A query that wraps the indexed column in a function can also block efficient seeking and complicate matching.
Data type conversion can spoil an otherwise good design. Match parameter type and length to the column. Check the plan for implicit conversion warnings and confirm the seek predicate, not just the index name. A plan that scans a filtered index can still be useful, but it answers a different performance question.
Keep Required Columns Available
Included columns can avoid lookups for common queries. Add only columns that materially reduce expensive reads. Each extra leaf column increases storage and write work. The filter column does not always need to be a key, but query shape and optimizer requirements can make including it useful. Test actual plans rather than relying on a blanket rule.
Consider the clustered key too. It is present in nonclustered index rows, so a narrow clustered key keeps filtered indexes compact. A wide key is paid for repeatedly. I review total row width and write frequency when a proposed filtered index looks magically small in a query plan.
Maintain Statistics and Observe Drift
Filtered statistics cover the qualifying subset, which is one reason the design can improve estimates. Data movement can make those statistics stale, particularly when active rows change rapidly while the full table is much larger. Check last update time, modification counts, and estimate quality for the important query.
If a status distribution shifts, the filtered index can grow or lose selectivity. Revisit it after product or retention changes. Do not rebuild on a calendar merely because the index has a filter. Let query performance, statistics quality, and page behavior guide maintenance. The best index is the one that still helps under today’s data, not last year’s screenshot.
Prove the Benefit of Filtered Indexes End to End
Compare the active query before and after with actual plans, logical reads, CPU, and duration across realistic parameter values. Include the write workload that changes the filtered columns. A reduction in reads that adds unacceptable update latency is not a complete win. Also check whether another index became redundant.
Filtered indexes work best when the business query has a clear, selective boundary. Keep that boundary explicit in the code and in the design notes. If the application asks a broader question, give it an appropriate broader access path. A small index is useful because it answers a small question very well, not because its page count looks charming.
Related reading on this blog: Introduction to Filtered Index: Improve performance with Filtered Index and What is Filtered Statistics?.
A filtered index is not a shortcut for every query, it is a path for provably matching queries.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
