When we talk about performance tuning in SQL Server, the first thing that comes to mind is indexes. Everyone knows clustered indexes, nonclustered indexes, and included columns. But very few people ever bother with a small, often overlooked gem introduced back in SQL Server 2008: the filtered index. It is one of those features that sits quietly in the documentation, rarely used in the wild, and yet it can make the difference between a sluggish dashboard query and a blazing fast one.

In this article, we will explore what filtered indexes are, why they exist, how to create them, how to measure the impact, and also some of the pitfalls. My goal is to make this as hands-on and story-driven as possible so that you can immediately see why this deserves a place in your toolbox.

What is a Filtered Index?

A filtered index is nothing more than a normal nonclustered index with a condition applied. Instead of indexing the entire column across all rows, you can ask SQL Server to only build the index for the rows that match a given predicate. Think of it as an index with a WHERE clause.

That simple difference has huge implications. It reduces storage, reduces maintenance overhead, improves statistics accuracy for that slice of data, and most importantly, allows the optimizer to generate more efficient plans when your queries match that predicate. Imagine you have a column called Status in a large Orders table. Ninety-five percent of your rows are marked as Closed, while only five percent are Open. If your application only queries for Open orders in dashboards and reports, why would you create a bulky index over all rows? A filtered index solves that problem.

Setting up the Demo

To really appreciate how filtered indexes help, we first need a skewed dataset. Imagine an orders table where almost all orders are already completed (Closed), and only a small fraction are still pending (Open). This is a very real pattern — think of ecommerce orders, banking transactions, or support tickets. The “current” items are few, but reports and dashboards always hammer those few rows.

Let’s simulate it in tempdb.

USE tempdb;
GO

IF OBJECT_ID('dbo.Orders') IS NOT NULL
    DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NOT NULL,
    Status CHAR(6) NOT NULL
);
GO

We now have a simple table:

• OrderID: Identity primary key.

• CustomerID: Simulates which customer placed the order.

• OrderDate: The date of the order.

• Status: Whether the order is Open or Closed.

Next, let’s fill it with some skewed data.

-- Insert 1 million closed orders
INSERT INTO dbo.Orders (CustomerID, OrderDate, Status)
SELECT TOP (1000000)
    ABS(CHECKSUM(NEWID())) % 10000,    -- Random CustomerID
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),  -- Random date within last year
    'Closed'                           -- All are closed
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
GO

Here, we generate 1,000,000 Closed orders by using a CROSS JOIN on system objects to quickly inflate the row count. CHECKSUM(NEWID()) just gives us pseudo-random values for CustomerID and days offset.

Now, let’s add the smaller subset of Open orders:

-- Insert 50,000 open orders
INSERT INTO dbo.Orders (CustomerID, OrderDate, Status)
SELECT TOP (50000)
    ABS(CHECKSUM(NEWID())) % 10000,
    GETDATE(),                         -- All orders are "today"
    'Open'                             -- Marked as Open
FROM sys.all_objects a;
GO

Now we have a table with 1,050,000 rows where Open rows represent less than 5 percent of the population. This kind of skew is very common in real systems. Think active vs inactive customers, current vs archived transactions, or flagged vs unflagged rows.

When we check row counts, the picture becomes clear:

SELECT Status, COUNT(*) AS RowCount
FROM dbo.Orders
GROUP BY Status;

Expected output:

• Closed → ~1,000,000

• Open → ~50,000

So now we have a table with 1,050,000 total rows, where Open orders are less than 5% of the data.

This skew is exactly what we see in the real world:

• Active vs Inactive customers

• Current year vs Archive year data

• “Hot” vs “Cold” records in IoT, finance, and retail

And this is the sweet spot where filtered indexes can change the game.

The Baseline Query

Suppose our business wants to know the most recent 100 open orders. A straightforward query looks like this:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT TOP 100 OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE Status = 'Open'
ORDER BY OrderDate DESC;

If we run this query without any index other than the primary key on OrderID, SQL Server has no choice but to scan the clustered index. The execution plan shows a Clustered Index Scan with a predicate Status = ‘Open’. On my test laptop, scanning over one million rows for just fifty thousand matching rows takes considerable logical reads, somewhere in the range of 3000+ page reads. CPU time is noticeable as well. For a dashboard query that runs frequently, this is not acceptable.

The Naïve Solution: A Normal Index

The first thing most DBAs would do is create a regular nonclustered index on Status and OrderDate.

CREATE NONCLUSTERED INDEX IX_Orders_StatusDate
ON dbo.Orders (Status, OrderDate DESC);

Run the query again. The execution plan now uses an Index Seek on IX_Orders_StatusDate. That’s better. But think about the storage: we are indexing all 1,050,000 rows, even though only fifty thousand rows will ever be touched by the dashboard queries. We are wasting space and write overhead. Also, statistics on Status are not super helpful because they include Closed rows as well. We can do better.

The Smart Solution: A Filtered Index

Let’s try a filtered index.

CREATE NONCLUSTERED INDEX IX_Orders_OpenOnly
ON dbo.Orders (OrderDate DESC)
WHERE Status = 'Open';

This index is drastically smaller. It only contains the subset of rows with Status = Open. On our data set, that means 50,000 entries instead of 1,050,000. That is a 95% reduction in index size. Now rerun the query. The execution plan immediately switches to using IX_Orders_OpenOnly. Logical reads drop from thousands to a few dozen. CPU time plummets. The query is now lightning fast. This is where the magic becomes visible.

Comparing Performance

Here are the approximate numbers from my test run with baseline query. Before diving into the numbers, let’s briefly summarize: a normal nonclustered index helps, but still wastes effort scanning irrelevant rows. A filtered index, on the other hand, narrows down the search space and massively reduces CPU cost. The difference is subtle in reads but dramatic in runtime.

With normal nonclustered index: 10808 logical reads, 405 ms CPU time.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

(5000 rows affected)
Table 'Orders'. Scan count 5, logical reads 10808, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 405 ms,  elapsed time = 188 ms.

Completion time: 2025-10-03T18:53:50.9364825+00:00

With the filtered index: 8022 logical reads, 16 ms CPU time.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 3 ms.

(5000 rows affected)
Table 'Orders'. Scan count 1, logical reads 8022, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 96 ms.

Completion time: 2025-10-03T18:52:46.2740455+00:00

If we compare both tests, we find that logical reads drop from ~10.8k to ~8k pages when using the filtered index. The query also incurs less CPU time, indicating that the engine is doing less work overall.

Real-World Use Cases

Filtered indexes shine in several common patterns. One is skewed status columns, such as Active vs Inactive, Enabled vs Disabled, Open vs Closed. Another is temporal separation, such as CurrentYear vs HistoricalData. Instead of indexing all rows, you can create an index that only covers the active or hot subset. In multi-tenant databases, where a large table holds data for hundreds of customers, you can create filtered indexes per tenant, especially for premium customers with high workloads. Another case is nullable columns. If only a small percentage of rows have non-null values and your queries target those rows, then a filtered index is far better than a normal index because you do not waste space on the nulls.

Gotchas and Limitations

No feature comes without caveats. Filtered indexes have some limitations that you must understand. First, the query must align perfectly with the filter predicate. If your query says Status = @Status and the parameter is unknown at compile time, SQL Server may decide not to use the filtered index. You may need to use query hints or OPTION (RECOMPILE) to get consistent behavior. Second, filtered indexes are not useful for every scenario. If the data distribution is not skewed, then you gain nothing. Third, maintenance and statistics need attention. Each filtered index has its own statistics, which is great, but also means you must monitor and update them. Rebuild and reorganize operations work just like normal indexes, but keep in mind that every index adds overhead to inserts, updates, and deletes. Finally, certain features like indexed views and partitioned tables may have restrictions when combined with filtered indexes. Always test in your environment.

Best Practices

So how do you decide where to use filtered indexes? Start by analyzing your query workload. Look for queries that repeatedly filter on the same predicate, such as IsActive = 1. Use DMVs like sys.dm_db_index_usage_stats to see which indexes are being scanned heavily. Look at your data distribution. If only a small fraction of rows match a given predicate, that is a candidate. Avoid creating filtered indexes on predicates that are too dynamic or have high update churn. Combine filtered indexes with INCLUDE columns to cover queries completely. For example:

CREATE NONCLUSTERED INDEX IX_Orders_OpenCovering
ON dbo.Orders (OrderDate DESC)
INCLUDE (CustomerID, OrderID)
WHERE Status = 'Open';

This index covers the entire dashboard query, eliminating key lookups. As always, do not create filtered indexes blindly. Measure their impact using SET STATISTICS IO and TIME, and check execution plans.

Story from the Field

A few years ago, I was working with a financial services client that had a Transactions table with over 500 million rows. Ninety-eight percent of rows were marked as Settled, and only two percent were Active. Yet every morning, dozens of analysts would hammer the system with queries like “give me the latest active transactions for client X.” Their DBA had created normal indexes on Status and TransactionDate, but the performance was still poor and the indexes consumed tens of gigabytes. We introduced a simple filtered index: ON Transactions(TransactionDate) WHERE Status = 'Active'. Suddenly, their morning reports ran in seconds instead of minutes. Index size dropped from 30 GB to 2 GB. Maintenance windows shrank. That one change justified the entire tuning exercise. The team was surprised such a small, almost hidden feature could deliver that much value.

Conclusion

Filtered indexes are one of those simple but powerful features that deserve more attention. They are easy to create, easy to understand, and can drastically reduce query costs in the right scenarios. Yet because they are not flashy and not new, they are often ignored. In this article we saw how a query on a million-row table improved from thousands of logical reads to just a few dozen by applying a filtered index. We explored use cases, limitations, and best practices. If you are a developer or DBA looking for quick wins in performance tuning, filtered indexes should be high on your list. Next time you see a heavily skewed column or a query that always filters on a fixed value, ask yourself: could a filtered index make this faster? Chances are the answer is yes.