Sparse and Filtered Indexes

Construct indexes on small subsets of data to drastically reduce index size and maintenance cost.

In this lesson, you will learn how to boost query performance and shrink index storage by creating indexes that only track a specific subset of your data.

The Problem: Excess Baggage

Up to this point, we have discussed indexes as structures that cover every row in a table. However, imagine you have a Sales table with 10 million rows, but your common queries only look for orders with a status of ‘Pending’. If only 500 rows are ‘Pending’, a standard non-clustered index is massive and inefficient. The database engine must maintain an index structure for all 10 million rows, wasting storage and slowing down INSERT operations because the index must be updated for every new sale, regardless of its status.

The Solution: Filtered Indexes

A Filtered Index (sometimes referred to as a sparse index in concept) allows you to define a WHERE clause when creating the index. By doing this, you instruct the database to ignore any rows that don’t meet your specific criteria.

Consider this example:

CREATE INDEX IX_PendingOrders 
ON Sales(OrderID)
WHERE Status = 'Pending';

Why This Works

Drastic Size Reduction: Because the index only contains entries for the 500 ‘Pending’ rows, the index tree is tiny compared to a full-table index. This means it can likely fit entirely in memory (RAM), leading to lightning-fast lookups.
Lower Maintenance: The database engine only updates this index when a row’s status changes to or from ‘Pending’. If you add 1,000 new ‘Completed’ orders, this specific index remains untouched, significantly reducing I/O overhead during write operations.
Improved Statistics: The database creates statistics based only on the filtered subset, which helps the query optimizer make much more accurate decisions when executing queries involving the Pending status.

By focusing your indexing strategy on the "hot" subset of your data rather than the entire table, you create a leaner, faster, and more efficient database environment.

Key Takeaway: Filtered indexes optimize performance by indexing only the subset of data that matters to your queries, saving storage space and reducing the maintenance burden on your database.