Filtered Indexes for Reporting

A filtered index is a nonclustered index with a WHERE clause: it indexes only the rows matching a predicate. It is smaller, cheaper to maintain, and it carries statistics computed over just that subset — which is often more valuable than the size saving.

Reporting workloads suit them well, because reports usually care about a slice: open orders, active customers, the current fiscal year, non-cancelled transactions.

-- Only the rows a "pending work" report ever looks at
CREATE NONCLUSTERED INDEX FIX_Orders_Pending
    ON dbo.Orders (RegionId, OrderDate)       -- placeholder table
    INCLUDE (CustomerId, Total)
    WHERE StatusId IN (1, 2);                 -- pending, in-progress

If 3% of the table is pending, that index is 3% of the size of the unfiltered equivalent and updates only when a row enters or leaves the filtered set.

The main use cases

A dominant status value. A table where 97% of rows are Closed and every report queries the other 3%. An unfiltered index on StatusId is nearly useless — the leading column has almost no selectivity. A filtered index on the 3% is precise.

Sparse columns. Indexing WHERE ApprovedByUserId IS NOT NULL when the column is null for most rows.

Enforcing conditional uniqueness. A genuinely useful side effect:

-- One active record per customer; historical rows unconstrained
CREATE UNIQUE NONCLUSTERED INDEX UX_Subscription_ActivePerCustomer
    ON dbo.Subscription (CustomerId)
    WHERE IsActive = 1;

Better statistics on a hot subset. The histogram describes only the filtered rows, so estimates for queries over that subset are much sharper. This matters even when the size saving does not.

The conditions for it to be used

Filtered indexes are more particular about matching than regular ones, and this is where they disappoint people.

The query predicate must be provably covered by the filter, at compile time. WHERE StatusId = 1 matches a filter of WHERE StatusId IN (1,2). WHERE StatusId = @Status does not — the optimizer cannot prove at compile time that @Status is 1 or 2, so it will not use the index and may emit an "Unmatched Indexes" warning (Module 2).

That is the single most common reason a filtered index sits unused in an application that parameterizes everything. Three ways around it:

-- (a) Literal in the query
SELECT RegionId, OrderDate, Total FROM dbo.Orders WHERE StatusId = 1 AND RegionId = @Region;

-- (b) Let the optimizer see the parameter value
SELECT RegionId, OrderDate, Total FROM dbo.Orders
WHERE StatusId = @Status AND RegionId = @Region
OPTION (RECOMPILE);

-- (c) Branch in the procedure so each branch has a literal
IF @Status = 1
    SELECT RegionId, OrderDate, Total FROM dbo.Orders WHERE StatusId = 1 AND RegionId = @Region;
ELSE
    SELECT RegionId, OrderDate, Total FROM dbo.Orders WHERE StatusId = @Status AND RegionId = @Region;

The filter column should usually be in the key or INCLUDE list too. Not strictly required for the index to be chosen, but it lets the plan re-verify the predicate and it widens the set of queries the index can serve. Cheap insurance.

Required SET options. Filtered indexes need ANSI_NULLS ON, QUOTED_IDENTIFIER ON and the rest of the standard set at both creation time and query time, otherwise the query errors or silently skips the index. Modern .NET clients set these correctly by default; older ODBC/OLE DB code paths and some ETL tools do not. If a filtered index works in SSMS and not from the application, check this first:

SELECT session_id, quoted_identifier, arithabort, ansi_nulls, ansi_warnings, concat_null_yields_null
FROM sys.dm_exec_sessions
WHERE session_id > 50;

A row from your application connection differing from your SSMS row also explains the classic "fast in SSMS, slow in the app" report — different SET options mean a different cached plan.

Filtered statistics without an index

If you only want the better estimates and not the index, create the statistic alone. Much cheaper:

CREATE STATISTICS ST_Orders_CurrentYear
    ON dbo.Orders (OrderDate, RegionId)
    WHERE OrderDate >= '2026-01-01';

Useful when one date slice of a large table has a very different distribution from the rest and estimates for current-year queries are consistently wrong.

What they will not do

A filtered index cannot filter on a computed column that is not persisted, cannot use OR across different columns in some cases, and cannot reference other tables. If your report's "active" concept is a join rather than a column, an indexed view (Module 6) is the tool, not a filtered index.

The short version

Filtered indexes fit reporting well because reports query a slice — open orders, current year — and you get a smaller index plus a histogram computed over just that subset. The condition to watch is that the optimizer has to prove the query's predicate falls inside the filter at compile time, so a parameterized WHERE StatusId = @Status will not match a filtered index unless you recompile or branch on a literal.