What is a filtered index?
Quick Answer
A filtered index has a WHERE clause, so it only indexes the subset of rows that match — CREATE INDEX IX_Orders_Open ON Orders(CustomerId) WHERE Status = 'Open'. It's smaller, cheaper to maintain, and often more selective than a full index, ideal when queries always target a minority of rows (open orders, non-deleted records, non-null values).
A filtered index is a normal non-clustered index with a WHERE clause, so it only covers the rows that match. The typical case is skewed data — if the application only ever queries open orders and those are 2% of the table, a filtered index on just those rows is small, selective and cheap to keep updated. It's also how you do things like “enforce a unique email but only among active users.” The catch is that the query predicate has to line up with the index filter for the optimiser to use it.
- Great for skewed data: 1% of rows are
Status='Open'but that's all the app queries — the filtered index is tiny. - Enforce uniqueness on a subset:
UNIQUE ... WHERE IsDeleted = 0. - Index only rows where a nullable column is populated:
WHERE PhoneNumber IS NOT NULL. - Caveats: the query's predicate must match (or be a superset of) the index filter for it to be used; parameterised queries sometimes don't match filtered indexes without
RECOMPILE; limited expression support in theWHERE.