With filtered rowstore indexes, SQL Server can build a nonclustered index that covers only the rows a query is likely to read. That changes the size of the index, the statistics attached to it, and the upkeep tied to INSERT, UPDATE, and DELETE activity. On tables where the busiest queries keep returning to the same narrow slice of data, that smaller index can do more good than one that tracks every row in the table. Its benefits come from trimming storage, lowering upkeep, and helping query performance when the filter matches a well-defined subset.
How Filtered Indexes Operate
SQL Server treats a filtered rowstore index as a nonclustered index on a table, but the WHERE clause changes which rows ever become part of that structure. That choice affects the number of index entries, the statistics tied to the index, and the upkeep tied to data changes that pass through the filtered slice. As we read through the mechanics, it helps to stay focused on that entry rule first. We are not building a separate table or a special storage format. We are building a narrower nonclustered index whose row membership is decided by the filter predicate.
What Rows Enter the Index
Regular nonclustered indexes track every row in the table. Filtered indexes do not. SQL Server checks the filter predicate and stores entries only for rows that satisfy it. Rows outside that subset never get an entry in that index, so the structure starts smaller from the moment we create it. At the leaf level, a nonclustered rowstore index stores the indexed columns, any included columns we added, and a row locator back to the base row. On a clustered table, that locator is the clustered index key. With a filtered index, we still get that same general storage layout, but only for rows that pass the filter.
To make that easier to read in code, we can build a small queue table and then add a filtered index that keeps only the pending rows:
CREATE TABLE dbo.WorkQueue
(
WorkItemId int NOT NULL IDENTITY(1,1),
AccountId int NOT NULL,
StatusCode varchar(20) NOT NULL,
SubmittedAt datetime2 NOT NULL,
ProcessedAt datetime2 NULL,
PayloadSize int NOT NULL,
CONSTRAINT PK_WorkQueue PRIMARY KEY CLUSTERED (WorkItemId)
);
GO
CREATE NONCLUSTERED INDEX IX_WorkQueue_Pending
ON dbo.WorkQueue (SubmittedAt)
INCLUDE (AccountId, PayloadSize)
WHERE ProcessedAt IS NULL;
GOWith that definition in place, we are telling SQL Server to keep index entries only for rows where ProcessedAt IS NULL. A row with a value in ProcessedAt does not belong in IX_WorkQueue_Pending, so SQL Server leaves it out of that index entirely. A row that starts out pending does belong, so an entry is stored. Later, if that same row gets processed and ProcessedAt changes from NULL to a datetime value, the row no longer fits the filter and its index entry is removed. Membership in the filtered index is tied directly to the predicate, not to a preference in the optimizer and not to the way we phrase the query text later.
We can also inspect the index definition and see that SQL Server stores the filter expression as part of the metadata:
SELECT
i.name,
i.index_id,
i.has_filter,
i.filter_definition
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.WorkQueue');Reading sys.indexes gives us a direct way to verify that the index really is filtered and to see the predicate attached to it. That helps tie the code back to the storage rule. If the filter says ProcessedAt IS NULL, then only that slice of the table gets represented in the index. Nothing outside that slice is available through that index because no entry was stored for those rows in the first place.
Query matching grows out of the same rule. We get the most value from a filtered index when our query is asking for rows that stay inside the filtered slice. If the index contains pending rows only, then a query that asks for pending rows can line up with it. If the query asks for all rows, or for a different slice, then that filtered index cannot represent the full result set on its own. That is why row membership comes first in the explanation. Before we talk about reads, seeks, or plan choices, we need to know what the index actually contains.
Why a Smaller Index Helps
Fewer qualifying rows mean fewer index entries and usually fewer leaf pages for SQL Server to read. That can reduce the amount of I/O tied to repeated queries that stay inside the filtered slice. Storage drops too, because the index is not carrying entries for the rest of the table. We can think of it as trimming away rows that do not help the repeated query family, then letting the index stay focused on the narrower set that does.
To get a sense of that size difference, we can compare page and row counts for the indexes on the table:
SELECT
i.name,
SUM(ps.row_count) AS indexed_rows,
SUM(ps.used_page_count) AS used_pages
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS ps
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.WorkQueue')
GROUP BY i.name
ORDER BY i.name;If dbo.WorkQueue holds a large history of completed rows and only a small pending slice, the filtered index row count should stay far below the clustered index row count. The page count usually falls with it. That smaller footprint is part of why a filtered index can serve a repeated subset query with less reading than a broader nonclustered index built across the full table.
Statistics are tied to this as well. Every index in SQL Server has a statistics object tied to it, and a filtered index creates statistics for only the rows inside that filtered slice. That gives the optimizer row distribution data for the smaller subset rather than for the entire table. If pending rows make up a tiny share of dbo.WorkQueue, those filtered statistics can give row estimates that fit that subset more closely than full-table statistics would. We are still dealing with the same table, but now the index and its statistics are both centered on the narrower group of rows.
Upkeep follows the same line of thought, SQL Server only has to maintain a filtered index when a data change affects rows that belong in that index or when a row crosses into or out of the filtered slice. Completed rows that never match ProcessedAt IS NULL do not need entries in IX_WorkQueue_Pending, so that index stays out of those rows. A full-table nonclustered index has no such boundary because every row belongs to it. That difference is part of why filtered indexes can take less storage and less maintenance for repeated subset queries.
Smaller size does not help every query against the table. It helps the query family that stays within the filtered subset we chose. That point ties the whole mechanic together. We create the filter to limit row membership, SQL Server stores entries only for that slice, the statistics follow that same slice, and then matching queries can benefit from the narrower structure.
Good Filters for Common Queries
Query habits should lead this part of the index choice. We want a row subset that keeps coming back in the same small family of reads, because that is where a filtered index earns its place. If the busiest queries keep returning to active rows, pending rows, or rows where a column is not NULL, we have a strong candidate for a filtered slice. SQL Server handles that well when the filter draws a firm boundary around rows that stay meaningful to that recurring query family.
Finding a Good Target Set
Hot row subsets usually come from data that splits into an active slice and a quieter remainder. Pending jobs versus completed jobs is a classic example. Active subscriptions versus expired subscriptions fits too. Rows with a populated email address can also make sense when the repeated query needs only contactable users. What ties those cases together is the reason we build the filter in the first place. We are matching the index to rows that the same queries keep returning to, not trimming rows just because we can.
That choice becomes easier to judge when we look at row share and query frequency side by side. If a table stores millions of rows but the busiest screen keeps asking for the small set that is still open, indexing the full table for that screen can be heavier than needed. On the other hand, if the supposed hot slice covers most of the table, the filter may not buy much. A good target set stays narrow enough to trim the index while still matching a query family that shows up regularly.
We can make that more concrete with an orders table where shipping staff spend most of their time on rows that still need shipment:
CREATE TABLE dbo.SalesOrder
(
SalesOrderId int NOT NULL IDENTITY(1,1),
CustomerId int NOT NULL,
OrderDate datetime2 NOT NULL,
ShipDate datetime2 NULL,
OrderTotal decimal(12,2) NOT NULL,
StatusCode varchar(20) NOT NULL,
CONSTRAINT PK_SalesOrder PRIMARY KEY CLUSTERED (SalesOrderId)
);
GO
CREATE NONCLUSTERED INDEX IX_SalesOrder_Unshipped
ON dbo.SalesOrder (OrderDate)
INCLUDE (CustomerId, OrderTotal, StatusCode)
WHERE ShipDate IS NULL;
GOWith that definition in place, we are saying that the repeated reads center on orders still waiting to ship. Rows that already have ShipDate filled in still belong in the table, but they do not need entries in IX_SalesOrder_Unshipped. That is the real test for a strong target set. We want a boundary that lines up with recurring reads and leaves a large share of less relevant rows outside the index.
NULL and non-NULL splits deserve extra attention because they show up all through business data. Contact fields, completion timestamps, approval timestamps, and external reference numbers all tend to start as NULL and then fill in later. If the repeated query asks only for rows where that value is present, or only for rows where it is still missing, that can turn into a very natural filtered slice.
Writing the Predicate
Filter text has to stay within the forms SQL Server supports. The filter lives in the WHERE clause of CREATE INDEX, and it is limited to a subset of single-table comparison logic. We want the predicate to stay direct and tied to base columns on that table. IS NULL, IS NOT NULL, equality checks, and range checks all fit naturally here.
Let’s see that with an invoice table where the repeated read centers on unpaid rows:
CREATE NONCLUSTERED INDEX IX_Invoice_Unpaid
ON dbo.Invoice (DueDate)
INCLUDE (CustomerId, BalanceDue)
WHERE BalanceDue > 0;
GOThat filter reads well because the rule is tied to a single table and a direct comparison on a base column. SQL Server can build the index for rows where BalanceDue > 0 without extra logic from elsewhere. We usually want that same direct style in filtered index definitions because it keeps the row subset easy to read and easier for the optimizer to match to the intended query family.
Some forms are not allowed. LIKE cannot appear in the filter. Views cannot have filtered indexes. Computed columns cannot appear in the filter expression. That means we should not plan around a filter such as email domains ending in a certain suffix or a computed status flag unless that logic has first been stored in a regular base column. If the business rule depends on several tables or on heavier expression logic, the filter has moved past what this feature is built to handle.
Data type conversion can trip us up too. SQL Server can raise an error if the filtered comparison forces a conversion on the left side of the operator. We want the converted value on the right side instead:
CREATE NONCLUSTERED INDEX IX_DeviceEvent_Recent
ON dbo.DeviceEvent (EventTime)
WHERE EventTime >= CONVERT(datetime2, '2026-01-01T00:00:00');
GOThat statement keeps EventTime on the left and places the conversion on the right. Small details like that can decide if index creation succeeds, so the safest habit is to keep the predicate in the plainest legal form we can.
Query matching also ties back to predicate wording. We want the repeated query to stay inside the filtered slice. If the filter is BalanceDue > 0, then a query asking for unpaid invoices fits that boundary. If the query asks for all invoices, that filtered index cannot cover the full row set. Good predicate text is not only about legal syntax. We are also drawing a boundary that the intended queries can stay within.
Choosing Index Keys With Included Columns
Row selection is only part of the job. After we settle the filtered subset, we still have to pick the index keys and any included columns with care. The filter decides which rows enter the index. The index keys still decide how those rows are ordered and how we search through them. If the repeated query filters active subscriptions by RenewalDate, joins on CustomerId, or sorts upcoming renewals first, we want the index keys to line up with that read route rather than treating the filter itself as the whole story.
That distinction helps explain why two filtered indexes with the same WHERE clause can behave very differently. Shared row membership does not mean equal value. We still build the index keys around the repeated query, and included columns help us return extra data from the leaf level without extra lookups for every qualifying row.
We can put a query next to an index to make that easier to read:
SELECT
SubscriptionId,
CustomerId,
RenewalDate,
PlanCode
FROM dbo.Subscription
WHERE CancelledAt IS NULL
AND RenewalDate < '2026-07-01'
ORDER BY RenewalDate;For that query, a filtered index that starts with RenewalDate is usually a better match than one that starts with CustomerId, because we are filtering and ordering by the renewal date inside the active slice.
CREATE NONCLUSTERED INDEX IX_Subscription_ActiveRenewal
ON dbo.Subscription (RenewalDate)
INCLUDE (CustomerId, PlanCode)
WHERE CancelledAt IS NULL;
GOWhat we are doing there is building the filtered subset around active subscriptions while still shaping the index for the date-driven read. CustomerId and PlanCode go into the included column list because the query returns them, not because they need to drive the search order.
There is also a helpful nuance around the filter column itself. SQL Server does not always need that filter column in the index keys or include list when the query predicate matches the filter and the query does not return that column. In the subscription example, CancelledAt defines the subset, but the actual read is driven by RenewalDate. That gives us room to build the index around the search and sort flow rather than reflexively putting the filter column into the indexed columns.
Unique filtered indexes belong in this same discussion because they follow the same column-picking logic with a narrower rule layered on top. If only active rows must stay unique, we can place that rule directly into a filtered unique index rather than forcing uniqueness across inactive history too:
CREATE UNIQUE NONCLUSTERED INDEX UX_Member_Email_Active
ON dbo.MemberAccount (EmailAddress)
WHERE DeactivatedAt IS NULL
AND EmailAddress IS NOT NULL;
GOThat lets us keep duplicate email values out of the active slice while leaving inactive history alone. SQL Server applies uniqueness only to rows inside the filter, which is why filtered unique indexes fit soft-delete tables so well.
Cases Where a Filtered Index Is the Wrong Tool
Some queries do not call for a filtered index at all. If the main problem is row estimation for a subset query, filtered statistics may handle that problem without adding another index structure. That can make sense when we want better cardinality estimates for a narrow slice but do not want extra index storage or added upkeep tied to data changes.
We can build filtered statistics directly like this:
CREATE STATISTICS ST_Invoice_OpenBalance
ON dbo.Invoice (DueDate)
WHERE BalanceDue > 0;
GOThat statement gives SQL Server subset statistics without creating a filtered index. We would lean in that direction when the optimizer needs a better read on row distribution for a narrow slice, but the read route itself does not call for another nonclustered index.
Broad query families can also make a filtered index a weak match. If the application asks for unpaid invoices in one place, all invoices in another, recently paid invoices somewhere else, and disputed invoices in yet another read path, a single filtered index may help only a small corner of that activity. Cross-table business rules are a poor fit too. SQL Server filtered indexes live on one table with one filter expression tied to that table. If the rule depends on a joined status table or on logic that lives outside the base row, we have moved outside the natural boundary of this feature.
Forced hints deserve care as well. A filtered index is safe only when the query can be answered from the slice stored in that index. If we force a filtered index onto a query that reaches beyond its row subset, SQL Server can reject the plan outright. That is why filtered indexes need a tight link to the query family they are meant to help. They fit best when the row subset stays stable, the predicate stays direct, and the repeated reads stay firmly inside that filtered boundary.
Conclusion
Filtered indexes change query behavior by limiting index membership to a chosen slice of rows, which means SQL Server stores fewer entries, keeps statistics focused on that slice, and spends less maintenance on data outside it. When we match that filtered slice to the repeated queries a table serves, we give the optimizer a tighter view of the data and give reads a smaller structure to scan or seek through, which is why filtered indexes can be such a practical fit for active rows, pending rows, and other focused parts of a table.
