Filtered Indexes

Filtered indexes provide a way to optimize query performance by indexing a subset of rows in a table. This is particularly useful when queries frequently access specific data based on a WHERE clause. By creating an index on only these relevant rows, the index size is reduced, and query plans can be more efficient.

When to Use Filtered Indexes

Filtered indexes are beneficial in scenarios such as:

Indexing rows with a specific status (e.g., OrderStatus = 'Shipped').
Indexing active records (e.g., IsActive = 1).
Indexing data within a specific date range.
Improving performance for queries that consistently apply a filter predicate.

Benefits of Filtered Indexes

Reduced Index Size: Smaller indexes require less storage space and can be maintained more efficiently.
Improved Query Performance: Queries that match the filter predicate can benefit from faster seeks.
Reduced Maintenance Overhead: Less data means fewer operations during DML statements (INSERT, UPDATE, DELETE).
Statistics Optimization: Filtered indexes can have their own statistics, leading to better query plan choices for filtered queries.

Syntax for Creating a Filtered Index

You can create a filtered index using the following T-SQL syntax:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC|DESC], ... )
WHERE filter_predicate;

Where:

index_name: The name of the filtered index.
table_name: The table on which to create the index.
column1 [ASC|DESC], ...: The columns to include in the index key.
filter_predicate: The condition that defines the subset of rows to be indexed.

Example: Creating a Filtered Index

Consider a SalesOrderHeader table where you frequently query orders with a status of 'Shipped'. You can create a filtered index to optimize these queries:

-- Create a filtered index on shipped orders
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_ShippedOrders
ON Sales.SalesOrderHeader (OrderDate, CustomerID)
WHERE Status = 'Shipped';

With this index, queries like the following would benefit:

SELECT OrderID, OrderDate, CustomerID
FROM Sales.SalesOrderHeader
WHERE Status = 'Shipped' AND OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

Considerations

Note: A filtered index is not a replacement for a full table index if your queries frequently access rows that do not meet the filter criteria. Carefully analyze your query workloads before implementing filtered indexes.

Filtered Index vs. Full Index

Feature	Filtered Index	Full Index
Data Indexed	Subset of rows defined by a predicate	All rows in the table
Index Size	Smaller	Larger
Maintenance	Lower overhead	Higher overhead
Query Optimization	Optimizes queries matching the predicate	Optimizes a wider range of queries

Managing Filtered Indexes

You can manage filtered indexes using standard T-SQL commands like ALTER INDEX and DROP INDEX, similar to regular indexes.

-- To drop the filtered index
DROP INDEX IX_SalesOrderHeader_ShippedOrders ON Sales.SalesOrderHeader;

Filtered indexes are a powerful tool for SQL Server performance tuning when applied correctly. They allow you to tailor your indexing strategy to specific query patterns, leading to significant performance gains.