Filtered Index in SQL Server

What is a Filtered Index?

A filtered index is a nonclustered index that stores only the rows matching a specified WHERE condition.

A normal nonclustered index covers every row in the table:

CREATE INDEX IX_Orders_Status
ON Orders(Status);

A filtered index covers only selected rows:

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

This index contains only pending orders. Completed, cancelled, and other orders are excluded.

A filtered index is useful when queries frequently work with a small subset of a large table.

Simple Example

Consider an Orders table containing 10 million rows:

Status	Number of rows
Completed	8,500,000
Cancelled	1,000,000
Pending	500,000

The application frequently retrieves pending orders:

SELECT OrderId, OrderDate, CustomerId
FROM Orders
WHERE Status = 'Pending';

Instead of creating an index containing all 10 million rows, we can create one containing only the 500,000 pending orders:

CREATE INDEX IX_Orders_Pending
ON Orders(OrderDate)
INCLUDE (OrderId, CustomerId)
WHERE Status = 'Pending';

This produces a smaller and more focused index.

Why Do We Need a Filtered Index?

A filtered index is useful when:

Only a small percentage of rows are queried regularly.
Most rows have common values, but queries focus on rare values.
A table contains many NULL values.
Only active records are normally accessed.
Historical data is rarely queried.
A queue contains a small number of unprocessed records.
A status column has a small important subset.
Uniqueness should apply only to certain rows.

Typical examples include:

Active users
Pending orders
Unprocessed messages
Unpaid invoices
Non-deleted records
Rows with non-null values
Current records in a history table

How Does a Filtered Index Work?

Suppose the table contains:

OrderId	Status	OrderDate
1	Completed	2026-09-01
2	Pending	2026-09-02
3	Cancelled	2026-09-03
4	Pending	2026-09-04
5	Completed	2026-09-05

Create this index:

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

The index contains only:

OrderDate	Row locator
2026-09-02	Order 2
2026-09-04	Order 4

The other rows are not stored in the index.

When SQL Server receives:

SELECT *
FROM Orders
WHERE Status = 'Pending'
  AND OrderDate >= '2026-09-01';

it can use the smaller filtered index rather than scanning the whole table.

Syntax

CREATE NONCLUSTERED INDEX IndexName
ON TableName(KeyColumn)
INCLUDE (AdditionalColumn1, AdditionalColumn2)
WHERE FilterCondition;

Example:

CREATE INDEX IX_Invoices_Unpaid
ON Invoices(DueDate)
INCLUDE (CustomerId, Amount)
WHERE IsPaid = 0;

Here:

DueDate is the index key.
CustomerId and Amount are included columns.
Only unpaid invoices are stored in the index.

Filtered Index vs Normal Index

Feature	Normal nonclustered index	Filtered index
Rows included	All table rows	Only matching rows
Size	Usually larger	Usually smaller
Storage	More	Less
Maintenance	All relevant rows	Only qualifying rows
Statistics	Based on all indexed rows	Based on filtered subset
Best use	General query patterns	Specific subsets
`WHERE` condition	Not required	Required
Write overhead	Usually higher	Often lower

Common Use Cases

1. Active Records

Many applications implement logical or soft deletion:

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Email VARCHAR(200),
    IsDeleted BIT NOT NULL
);

Most queries retrieve only active customers:

SELECT CustomerId, CustomerName
FROM Customers
WHERE IsDeleted = 0;

Create:

CREATE INDEX IX_Customers_Active
ON Customers(CustomerName)
INCLUDE (CustomerId, Email)
WHERE IsDeleted = 0;

Only active customers are stored in the index.

2. Non-NULL Values

Suppose most employees do not have a resignation date:

SELECT EmployeeId, ResignationDate
FROM Employees
WHERE ResignationDate IS NOT NULL;

Create:

CREATE INDEX IX_Employees_ResignationDate
ON Employees(ResignationDate)
WHERE ResignationDate IS NOT NULL;

Only employees with a resignation date are indexed.

3. Pending Queue Records

Suppose a message table contains millions of processed messages but only a few pending messages:

SELECT TOP (100) MessageId, Payload
FROM MessageQueue
WHERE IsProcessed = 0
ORDER BY CreatedDate;

Create:

CREATE INDEX IX_MessageQueue_Pending
ON MessageQueue(CreatedDate)
INCLUDE (MessageId, Payload)
WHERE IsProcessed = 0;

SQL Server can efficiently find the oldest pending messages.

4. Unpaid Invoices

SELECT CustomerId, DueDate, Amount
FROM Invoices
WHERE IsPaid = 0
  AND DueDate < SYSDATETIME();

Create:

CREATE INDEX IX_Invoices_Unpaid_DueDate
ON Invoices(DueDate)
INCLUDE (CustomerId, Amount)
WHERE IsPaid = 0;

This is effective when unpaid invoices represent a relatively small portion of the table.

Filtered Unique Index

A filtered unique index enforces uniqueness only for rows satisfying its filter.

Common Example: Unique Values While Allowing Multiple NULLs

Suppose email must be unique when supplied, but many users may have no email:

CREATE UNIQUE INDEX UX_Users_Email_NotNull
ON Users(Email)
WHERE Email IS NOT NULL;

This permits:

UserId	Email	Result
1	`NULL`	Allowed
2	`NULL`	Allowed
3	`syed@example.com`	Allowed
4	`syed@example.com`	Rejected

The uniqueness rule applies only to non-null email values.

Active-Record Uniqueness

Suppose usernames must be unique only among active accounts:

CREATE UNIQUE INDEX UX_Users_Active_Username
ON Users(Username)
WHERE IsDeleted = 0;

A deleted user’s username can be reused, depending on application requirements.

Query Predicate Must Match the Filter

SQL Server is most likely to use a filtered index when the query’s condition logically guarantees that only rows contained in the index are required.

Filtered index:

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

Matching query:

SELECT OrderId, OrderDate
FROM Orders
WHERE Status = 'Pending';

The query matches the filter.

Non-matching query:

SELECT OrderId, OrderDate
FROM Orders
WHERE Status = 'Completed';

The index cannot be used because completed rows are not present.

Another non-matching query:

SELECT OrderId, OrderDate
FROM Orders;

The filtered index cannot return all orders because it contains only pending rows.

Filtered Index and Parameterized Queries

Filtered indexes can be more difficult to use with parameterized queries.

Filtered index:

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

Parameterized query:

SELECT OrderId, OrderDate
FROM Orders
WHERE Status = @Status;

At compilation time, SQL Server must create a plan that remains correct for possible values such as:

Pending
Completed
Cancelled

The filtered index contains only Pending rows, so SQL Server may not select it for a reusable plan.

Possible approaches include:

OPTION (RECOMPILE);

Example:

SELECT OrderId, OrderDate
FROM Orders
WHERE Status = @Status
OPTION (RECOMPILE);

This allows SQL Server to compile the query using the current parameter value.

Another approach is to use a query with a literal value when appropriate:

WHERE Status = 'Pending'

However, recompilation and query design must be evaluated carefully. Do not add OPTION (RECOMPILE) everywhere because compilation also has a cost.

Filter Column: Key or Included Column?

Consider:

CREATE INDEX IX_Orders_Pending
ON Orders(OrderDate)
INCLUDE (OrderId, CustomerId)
WHERE Status = 'Pending';

Status is used in the filter but is not necessarily required as a key column because all rows in this index already have the same status.

However, depending on the query, statistics, and execution-plan requirements, including the filter column can sometimes help SQL Server use the index reliably.

Example:

CREATE INDEX IX_Orders_Pending
ON Orders(OrderDate)
INCLUDE (OrderId, CustomerId, Status)
WHERE Status = 'Pending';

A particularly important case is an IS NULL filtered index. If SQL Server does not choose the index as expected, including the filtered column can help:

CREATE INDEX IX_Employees_Current
ON Employees(EmployeeId)
INCLUDE (EndDate)
WHERE EndDate IS NULL;

Always verify the actual execution plan.

Supported Filter Conditions

Filtered indexes support relatively simple comparison predicates, such as:

WHERE IsDeleted = 0

WHERE Status = 'Pending'

WHERE EndDate IS NULL

WHERE Email IS NOT NULL

WHERE Amount > 0

Filters can also combine supported predicates in appropriate forms.

However, filtered-index expressions have restrictions. They do not support arbitrary complex logic.

For example, filters cannot normally use:

LIKE
Subqueries
Nondeterministic functions
User-defined functions
Expressions involving data from another table
Dynamic conditions such as “today’s date”

For example, this is not a suitable filtered-index definition:

WHERE OrderDate >= GETDATE()

GETDATE() changes over time and is nondeterministic.

Instead, use a stable column such as a status flag when appropriate:

WHERE IsCurrent = 1

The application must then maintain that flag correctly.

Filtered Index Statistics

SQL Server creates statistics specifically for the filtered subset.

For example:

WHERE Status = 'Pending'

The statistics describe pending rows rather than the entire table.

This can improve cardinality estimates for queries operating on that subset, particularly when the overall table has highly uneven data distribution.

Better estimates can help SQL Server choose:

Index Seek vs Scan
Join type
Join order
Memory grant
Parallel or serial execution

Advantages of Filtered Indexes

1. Smaller Index Size

Only qualifying rows are stored.

2. Faster Targeted Queries

SQL Server reads fewer index pages.

3. Lower Storage Requirement

The index consumes less disk space than a full-table index.

4. Reduced Maintenance Cost

Only rows entering, leaving, or changing within the filtered subset affect the index.

5. Better Filtered Statistics

Statistics describe the relevant subset more accurately.

6. Supports Conditional Uniqueness

A filtered unique index can enforce uniqueness only for selected rows.

7. Better Cache Efficiency

A smaller index requires fewer pages and is more likely to remain in memory.

Disadvantages and Limitations

1. Supports Only Matching Queries

The query condition must match or logically imply the index filter.

2. Parameterized Queries May Not Use It

SQL Server may avoid the index when a parameter could contain values outside the filtered subset.

3. Limited Filter Expressions

Complex and nondeterministic expressions are not allowed.

4. Not Suitable for Every Data Distribution

If the filter includes most table rows, the benefit may be small.

5. Additional Write Overhead

Although smaller than a full index, it must still be maintained.

6. Changing Status Can Modify Index Membership

Consider:

UPDATE Orders
SET Status = 'Completed'
WHERE OrderId = 101;

If the row was previously pending, SQL Server must remove it from the filtered index.

Likewise, changing a completed order to pending inserts it into the index.

Normal Index or Filtered Index?

Assume:

The table contains 10 million orders.
9.5 million are completed.
500,000 are pending.
The main query retrieves pending orders.

A filtered index is a strong candidate:

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

However, if almost all rows are pending, the filtered index may offer little benefit over a normal index.

A filtered index is most beneficial when:

The subset is significantly smaller.
The subset is queried frequently.
The query predicate is predictable and stable.

How to Check Whether It Is Used

Display the actual execution plan and run the query:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderId, OrderDate
FROM Orders
WHERE Status = 'Pending'
  AND OrderDate >= '2026-01-01';

Look for:

Index Seek
The filtered index’s name
Logical reads
Key Lookups
Estimated versus actual row counts

Do not assume the index is useful simply because it exists.

Best Practices

Create filtered indexes for small, frequently accessed subsets.
Use stable and predictable filter conditions.
Ensure query predicates match the index filter.
Consider included columns to cover important queries.
Use filtered unique indexes for conditional uniqueness.
Test parameterized queries carefully.
Check actual execution plans.
Measure logical reads before and after creating the index.
Avoid creating many overlapping filtered indexes.
Monitor the write cost when rows frequently enter and leave the filtered subset.

Interview Key Points

A filtered index is a nonclustered index containing only rows matching a WHERE condition.
It is smaller than a full-table index.
It improves queries targeting a small, well-defined subset.
Typical examples include active, pending, unpaid, unprocessed, and non-null records.
The query predicate must match or logically imply the index filter.
Filtered statistics describe only the indexed subset.
A filtered unique index supports conditional uniqueness.
Parameterized queries may not always use a filtered index.
Complex or nondeterministic filter expressions are restricted.
Use execution plans and STATISTICS IO to confirm the performance benefit.

What is a filtered index?

Filtered Index in SQL Server

What is a Filtered Index?

Simple Example

Why Do We Need a Filtered Index?

How Does a Filtered Index Work?

Syntax

Filtered Index vs Normal Index

Common Use Cases

1. Active Records

2. Non-NULL Values

3. Pending Queue Records

4. Unpaid Invoices

Filtered Unique Index

Common Example: Unique Values While Allowing Multiple NULLs

Active-Record Uniqueness

Query Predicate Must Match the Filter

Filtered Index and Parameterized Queries

Filter Column: Key or Included Column?

Supported Filter Conditions

Filtered Index Statistics

Advantages of Filtered Indexes

1. Smaller Index Size

2. Faster Targeted Queries

3. Lower Storage Requirement

4. Reduced Maintenance Cost

5. Better Filtered Statistics

6. Supports Conditional Uniqueness

7. Better Cache Efficiency

Disadvantages and Limitations

1. Supports Only Matching Queries

2. Parameterized Queries May Not Use It

3. Limited Filter Expressions

4. Not Suitable for Every Data Distribution

5. Additional Write Overhead

6. Changing Status Can Modify Index Membership

Normal Index or Filtered Index?

How to Check Whether It Is Used

Best Practices

Interview Key Points

Keep Learning - More From This Category