SQL Filtered Index is a new feature introduced in Server 2008. A Filtered Index in SQL Server is nothing but a non clustered along with Where Clause. Because of the where clause, it will perform on a portion of records (records match where condition).
Before we go into examples of creating and deleting Filtered Indexes in SQL Server, please remember these points.
- Filtered Indexes will significantly improve the query performance because it is smaller than the Non-Clustered (works on Full table)
- The Filtered Indexes’ maintenance cost is very low because it is small.
- A filtered Index will reduce the Disk storage space. So, whenever possible, try them.
We created three tables, Employee1, Employee2, and Employee3 tables with the below-shown SQL Server data:
If you want to use the same data that we used in this filtered index example, then use the below query
USE [AdventureWorksDW2014] GO SELECT Cust.CustomerKey, Cust.[FirstName] + ' ' + Cust.[LastName] AS [FullName] ,Cust.[EmailAddress] ,Cust.[YearlyIncome] ,SUM([OrderQuantity]) AS [OrderQuantity] ,SUM([UnitPrice]) AS [UnitPrice] ,SUM([ExtendedAmount]) AS [ExtendedAmount] ,SUM([ProductStandardCost]) AS [ProductStandardCost] ,SUM([TotalProductCost]) AS [TotalProductCost] ,SUM([SalesAmount]) AS [SalesAmount] ,SUM([TaxAmt]) AS [TaxAmt] ,SUM([Freight]) AS [Freight] FROM DimCustomer AS Cust JOIN [FactInternetSales] on Cust.CustomerKey = FactInternetSales.CustomerKey GROUP BY Cust.CustomerKey, Cust.[FirstName], Cust.[LastName] ,Cust.[EmailAddress], Cust.[YearlyIncome] ORDER BY Cust.CustomerKey
Create Filtered Indexes in SQL Server
Before we start creating a Filtered Index, let me show you the Execution plan of the Query. The below statement will select all the records from the Employee1 table.
Create Non Clustered Index
Before we start creating the Filtered, let me create a basic Non Clustered on the Sales Amount column. We already explained the creation of the Non Clustered in our previous article. So, please refer to the same.
The below statement will create a Non Clustered Index on Sales Amount Column present in the Employee2 table.
CREATE NONCLUSTERED INDEX IX_Employee2_SalesAmount ON Employee2 (SalesAmount)
Messages -------- Command(s) completed successfully.
Now, we will compare the performance of a query without Non Clustered (Employee 1) and With Non Clustered (Employee 2). The below Where clause statement selects Customer Key and Sales Amount from Employee1 and Employee2 table
SELECT [CustomerKey], [SalesAmount] FROM [Employee1] WHERE [SalesAmount] > 2000; SELECT [CustomerKey], [SalesAmount] FROM [Employee2] WHERE [SalesAmount] > 2000;
If you observe the execution plan, there is a significant difference in the performance after we created a Non Clustered.
Create a Filtered Index in SQL Server
In this example, we will show you how to add them to an existing table. And the syntax is:
CREATE NONCLUSTERED INDEX Index_Name ON Table_Name (Column_Name(s) ASC/DESC) WHERE Some_Condition
The below statement will create a Filtered Index on Sales Amount Column present in the Employee 3 table
CREATE NONCLUSTERED INDEX IX_Employee3_SalesAmount ON Employee3 (SalesAmount) WHERE [SalesAmount] > 1000
Messages --------- Command(s) completed successfully.
This time, we will compare the performance of a query without Non Clustered (Employee 1), With Non Clustered (Employee 2), and With Filtered. The below statement will select Customer Key and Sales Amount from Employee1, Employee2, and Employee3 table.
If you observe the execution plan in the above screenshot, there is a significant difference in the performance after we created a Filtered Index in SQL.
The following screenshot will show you all of them that we created until now.
Create Composite Filtered Index in SQL Server
it allows you to create on multiple columns called composite. So, you can point the same index to Multiple columns. In this example, we are adding it to the Sales Amount column, and Yearly Income column
CREATE NONCLUSTERED INDEX IX_Employee4_SalesAmount ON Employee4 ([YearlyIncome], SalesAmount) WHERE [SalesAmount] > 1000
Messages ------- Command(s) completed successfully.
How to find Information about them?
You can use the sp_helpindex stored procedure to get the information about all the indexes present in a table.
EXECUTE SP_HELPINDEX Table_Name
Let me use the sp_helpindex stored procedure to check the indexes on Employee1, Employee2, Employee3, and Employee4 tables.
Create Filtered Indexes using SSMS
In this sample, we show you how to create SQL Filtered Indexes using the Management Studio. To do so, please expand the table you want to create and Right-click on the folder to open the context menu. Next, please select New and then select the Non-Clustered.. option
Once you choose the option, a new window will open.
Please change the Name as per your requirements. Click on the Add button to select columns.
Next, select the column name on which you want to specify it. For now, we are choosing the Yearly Income column.
Please change the sort order.
Next, go to the Filter page and write your condition
Delete Filtered Indexes in SQL Server
Please use the DROP INDEX statement to delete or drop Non Clustered Filtered Indexes.
DROP INDEX Employee3.IX_Employee3_SalesAmount
Messages -------- Command(s) completed successfully.
Use SSMS to Delete
To delete, select the table on which your Filtered Index is present. Next, expand the Indexes folder, right-click on the key name, and choose the Delete option.
Once you choose the Delete option, the Delete Object window will open. Click OK to delete.