Filtered indexes and computed columns

Target a meaningful workload subset.

UnderstandStudy an examplePractise

Understand the concept

A filtered index stores only rows satisfying its predicate. A small Pending subset can benefit from a smaller index and subset-specific statistics. The query must imply the filter; compilation may not be able to prove that for parameterised predicates. Filter syntax is restricted: simple comparisons are supported, not arbitrary LIKE or complex expressions.

A computed column names an expression; PERSISTED stores the value. Indexability depends on determinism, precision and required SET options. A computed column cannot be used in a filtered-index predicate. Check documented restrictions and test in isolation. Building production indexes can consume storage and acquire locks.

WHEN TO USE IT

Optimise sparse statuses and reusable expressions.

COMMON PITFALL

Assuming every parameterised query can use a filtered index.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT OrderId, OrderDate FROM lab.SalesOrder WHERE Status = 'Pending';

Defines the subset containing 104 and 112.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Build a temporary filtered-index candidate for zero-stock products.

Show the solution and explanation

Worked solution

DROP TABLE IF EXISTS #OutOfStock;
SELECT * INTO #OutOfStock FROM lab.Product;
CREATE INDEX IX_EmptyStock ON #OutOfStock(ProductId) INCLUDE (ProductName) WHERE Stock = 0;

This is a design candidate, not an expected speedup on a tiny lab.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.