Filtered Indexes in SQL Server

A FILTERED index is a nonclustered index with a WHERE clause.

It only contains the rows that match the filter, so it is SMALLER, FASTER to maintain, and has more accurate statistics.

CREATE INDEX IX_Name ON dbo.Table (Columns) WHERE <simple predicate>;

Great for:

• "Hot" subsets : Status = 'Pending' (5% of rows), IsDeleted = 0
• Sparse columns : CouponCode IS NOT NULL (most rows are NULL)
• Unique values that allow many NULLs (UNIQUE ... WHERE col IS NOT NULL)

• Limits: simple comparisons only (=, <>, >, <, IN, IS NULL, IS NOT NULL, AND). No OR, no functions, no computed columns, no LIKE.

The demo data on this site is deliberately small (300 orders and 50 customers) so the script runs instantly. With so few rows, SQL Server may choose a scan where a big table would use a seek, and the logical reads are tiny. To see the real performance difference, change TOP (300) to TOP (300000) and TOP (50) to TOP (50000) in the demo data script.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Lesson tables

DROP TABLE IF EXISTS dbo.FiltOrders;
DROP TABLE IF EXISTS dbo.FiltCustomers;

SELECT * INTO dbo.FiltOrders FROM dbo.BigOrdersData;
ALTER TABLE dbo.FiltOrders ADD CONSTRAINT PK_FiltOrders PRIMARY KEY CLUSTERED (OrderID);

SELECT CustomerID, CustomerName, Email, City,
       -- PAN-like ID number: only some customers have given it (others NULL)
       CAST(CASE WHEN CustomerID % 10 = 0 THEN CONCAT('ABCDE', RIGHT(CONCAT('0000', CustomerID), 5), 'F') END AS VARCHAR(12)) AS PANNumber
INTO dbo.FiltCustomers
FROM dbo.BigCustomersData;
ALTER TABLE dbo.FiltCustomers ADD CONSTRAINT PK_FiltCustomers PRIMARY KEY CLUSTERED (CustomerID);
GO

-- Data distribution
SELECT Status, COUNT(*) AS Orders, CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS DECIMAL(5,2)) AS Pct
FROM dbo.FiltOrders GROUP BY Status;

SELECT SUM(CASE WHEN CouponCode IS NULL THEN 1 ELSE 0 END) AS NoCoupon,
       SUM(CASE WHEN CouponCode IS NOT NULL THEN 1 ELSE 0 END) AS WithCoupon
FROM dbo.FiltOrders;
GO

(300 rows affected)
(50 rows affected)

Part A: FULL Index vs Filtered Index for "Pending" orders

Full index: all 300 rows

CREATE INDEX IX_FiltOrders_Status_Full
    ON dbo.FiltOrders (Status, OrderDate) INCLUDE (CustomerID, Amount);

Filtered index: only pending rows (~15,000)

CREATE INDEX IX_FiltOrders_Pending
    ON dbo.FiltOrders (OrderDate) INCLUDE (CustomerID, Amount)
    WHERE Status = 'Pending';

Compare sizes

SELECT i.name AS IndexName, i.filter_definition,
       SUM(ps.row_count) AS RowsInIndex, SUM(ps.used_page_count) AS Pages
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID('dbo.FiltOrders') AND i.index_id > 1
GROUP BY i.name, i.filter_definition;
GO

SET STATISTICS IO ON;
GO

Query that matches the filter: the optimizer picks the small filtered index

SELECT OrderID, OrderDate, CustomerID, Amount
FROM dbo.FiltOrders
WHERE Status = 'Pending' AND OrderDate >= '2025-06-01'
ORDER BY OrderDate;

Table 'FiltOrders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Force the full index to compare reads.

Reads are similar for this query, because both indexes can seek.

The real win is SIZE (compare Pages in A3): ~30x smaller, less memory, faster index maintenance on INSERT/UPDATE, and more accurate statistics.

SELECT OrderID, OrderDate, CustomerID, Amount
FROM dbo.FiltOrders WITH (INDEX (IX_FiltOrders_Status_Full))
WHERE Status = 'Pending' AND OrderDate >= '2025-06-01'
ORDER BY OrderDate;
GO

SET STATISTICS IO OFF;
GO

Table 'FiltOrders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Part B: Sparse Column (mostly NULL)

CREATE INDEX IX_FiltOrders_Coupon
    ON dbo.FiltOrders (CouponCode) INCLUDE (Amount)
    WHERE CouponCode IS NOT NULL;      -- only ~12 rows instead of 300

SET STATISTICS IO ON;
SELECT CouponCode, COUNT(*) AS TimesUsed, SUM(Amount) AS Revenue
FROM dbo.FiltOrders
WHERE CouponCode IS NOT NULL
GROUP BY CouponCode
ORDER BY TimesUsed DESC;
SET STATISTICS IO OFF;
GO

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'FiltOrders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Part C: "soft DELETE" pattern - index only the live rows

CREATE INDEX IX_FiltOrders_Live_Customer
    ON dbo.FiltOrders (CustomerID, OrderDate) INCLUDE (Amount)
    WHERE IsDeleted = 0;

-- App queries always include IsDeleted = 0 -> uses the filtered index
SELECT OrderID, OrderDate, Amount
FROM dbo.FiltOrders
WHERE CustomerID = 43 AND IsDeleted = 0;
GO

Part D: UNIQUE Filtered Index - unique values, but many NULLs allowed

A normal UNIQUE constraint allows only One NULL -> this fails (many NULL PANs)

BEGIN TRY
    ALTER TABLE dbo.FiltCustomers ADD CONSTRAINT UQ_FiltCustomers_PAN UNIQUE (PANNumber);
END TRY
BEGIN CATCH
    -- The Messages tab also shows the first error: 1505 "duplicate key ... (<NULL>)"
    SELECT 'UNIQUE constraint failed' AS Result, ERROR_MESSAGE() AS Msg;
END CATCH;

Filtered unique index: uniqueness only for rows that Have a Pan

CREATE UNIQUE INDEX UX_FiltCustomers_PAN
    ON dbo.FiltCustomers (PANNumber)
    WHERE PANNumber IS NOT NULL;

-- Test : another NULL is fine, a duplicate PAN is rejected
INSERT INTO dbo.FiltCustomers (CustomerID, CustomerName, Email, City, PANNumber)
VALUES (60001, 'New Customer 1', 'n1@mail.com', 'Pune', NULL);          -- OK

BEGIN TRY
    INSERT INTO dbo.FiltCustomers (CustomerID, CustomerName, Email, City, PANNumber)
    VALUES (60002, 'New Customer 2', 'n2@mail.com', 'Pune', 'ABCDE00010F');   -- duplicate of customer 10
END TRY
BEGIN CATCH
    SELECT 'Duplicate PAN rejected' AS Result, ERROR_MESSAGE() AS Msg;
END CATCH;
GO

(1 row affected)
(0 rows affected)

Part E: Gotcha - Parameters and Filtered Indexes

With a parameter / variable, the plan must work for ANY value, so a
filtered index for 'Pending' cannot be used safely.

DECLARE @Status VARCHAR(10) = 'Pending';

Filtered index NOT used (value unknown at compile time)

SELECT OrderID, OrderDate FROM dbo.FiltOrders WHERE Status = @Status AND OrderDate >= '2025-06-01';

Option (recompile) compiles with the actual value -> filtered index can be used

SELECT OrderID, OrderDate FROM dbo.FiltOrders WHERE Status = @Status AND OrderDate >= '2025-06-01'
OPTION (RECOMPILE);

Or write the literal value in the query (common in app code for fixed filters)

SELECT OrderID, OrderDate FROM dbo.FiltOrders WHERE Status = 'Pending' AND OrderDate >= '2025-06-01';
GO

Part F: Required SET Options

Inserts/updates on a table with a filtered index FAIL if the session has
wrong settings. SSMS defaults are correct. Required:
  ANSI_NULLS ON, QUOTED_IDENTIFIER ON, ANSI_PADDING ON, ANSI_WARNINGS ON,
  ARITHABORT ON, CONCAT_NULL_YIELDS_NULL ON, NUMERIC_ROUNDABORT OFF
(Old sqlcmd versions default QUOTED_IDENTIFIER OFF -> use sqlcmd -I)

-- List all filtered indexes in the database
SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName, filter_definition
FROM sys.indexes
WHERE has_filter = 1;
GO

Practice Questions

1. Create a filtered index for orders with Amount > 40000 (high value orders) and query them.
2. Add Email to FiltCustomers as unique-but-optional using a filtered unique index.
3. Write a query with "WHERE Status IN ('Pending', 'Shipped')" - create a filtered index that supports it.
4. Try to create a filtered index with "WHERE YEAR(OrderDate) = 2025". Read the error. How would you fix it?