Bits of .NET

Regular index on status column with 95% ‘active’ rows is inefficient. Filter index to index only relevant rows.

Problem – Full Index:

-- Index includes ALL rows (95% active, 5% inactive)
CREATE INDEX IX_Orders_Status ON Orders(Status);

-- Query only needs inactive
SELECT * FROM Orders WHERE Status = 'inactive';
-- Reads huge index to find 5% of rows

Solution – Filtered Index:

-- Index ONLY inactive rows (5% of table)
CREATE INDEX IX_Orders_Inactive 
ON Orders(OrderDate, CustomerId)
WHERE Status = 'inactive';

-- Same query now hits tiny, fast index!
SELECT * FROM Orders WHERE Status = 'inactive';

More Examples:

-- Index only unprocessed records
CREATE INDEX IX_Queue_Pending
ON JobQueue(CreatedAt)
WHERE ProcessedAt IS NULL;

-- Index only high-value orders
CREATE INDEX IX_Orders_HighValue
ON Orders(CustomerId, OrderDate)
WHERE Total > 1000;

Benefits: Smaller index = faster reads, less storage, faster writes!