SQL Server Filtered Index

In previous post, we have learnt about Index and its types. In this post, we will learn about filtered index. 
In SQL Server, a filtered index is an index that includes only a subset of rows based on a filter predicate. This can be useful when you want to create an index for a specific subset of data, improving query performance for a particular range or condition. Here's how you can create a filtered index:

CREATE INDEX [IndexName]
ON TableName (Column1, Column2, ...)
WHERE FilterPredicate;

Let's break down this syntax:

• CREATE INDEX: This statement is used to create an index, and it can be either a clustered or non-clustered index.
• IndexName: Replace this with a meaningful name for your index. It's optional, and if you omit it, SQL Server will generate a name.
• ON TableName: Specify the name of the table on which you want to create the filtered index.
• (Column1, Column2, ...): List the columns on which the index will be created.
• WHERE FilterPredicate: Specify the filter condition to include only a subset of rows in the index. This predicate defines the criteria for the filtered index.

Example. Suppose you have a table called Orders with columns OrderID, OrderDate, and Status, and you want to create a filtered index for rows where the status is 'Shipped':

CREATE INDEX IX_Orders_Shipped
ON Orders (OrderDate)
WHERE Status = 'Shipped';

This will create a non-clustered filtered index named IX_Orders_Shipped on the Orders table, using the OrderDate column and including only rows where the Status is 'Shipped'.

Filtered indexes can be particularly useful when you have a large table, and you are frequently querying a specific subset of the data. They can improve query performance by reducing the size of the index and making it more targeted to the data that is commonly queried.

Points to remember:

• Filtered indexes are available in SQL Server Enterprise, Developer, and Evaluation editions, starting from SQL Server 2008.
• They are not supported in SQL Server Express or SQL Server Compact editions.

In the next post we will learn about Alter Index in SQL Server.