Why should you care?

If 95% of rows are Closed, an index on open rows is smaller and faster for the queue that only cares about Open.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
IF COL_LENGTH('dbo.Orders', 'Status') IS NULL
    ALTER TABLE dbo.Orders ADD Status VARCHAR(10) NOT NULL CONSTRAINT DF_Orders_Status DEFAULT ('Open');
CREATE NONCLUSTERED INDEX IX_Orders_Open_City
ON dbo.Orders (City, OrderId)
WHERE Status = 'Open';
SELECT OrderId, City
FROM dbo.Orders
WHERE Status = 'Open' AND City = N'Pune';

What happened?

• The index leaf holds only Open rows.
• The query’s Status = 'Open' predicate lets the optimizer use the filtered index.

Practice next

1. Add Status if needed and create the filtered index.
2. Run the Open + City query with actual plan.
3. Query Status = 'Closed' and note it cannot use this index.
4. Filter WHERE Email IS NOT NULL on Customers.
5. Index only InStock = 1 products.

Remember

Filtered indexes store a row subset. Great for hot status values. Query predicates must align.

Open order queue

Fulfillment only works DataVerse Status = Open.

Outcome: Tiny filtered index beats a fat full-table index.