Filtered Indexes in SQL

Filtered Index is a new feature introduced in SQL Server 2008. A Filtered Index in SQL Server is nothing but a non clustered Indexes along with Where Clause. Because of the where clause, indexing 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

  • SQL Filtered Indexes will significantly improve the query performance because it is smaller than the Non-Clustered Index (works on Full table)
  • The maintenance cost of the Filtered Indexes is very less because it is small.
  • SQL Filtered Index will reduce the Disk storage space. So, whenever possible try Filtered Indexes

We created three tables Employee1, Employee2, and Employee3 tables with below-shown SQL Server data:

Filtered Indexes in SQL Server 1

If you want to use the same data that we used in this filtered index example, then use 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 SQL Server Filtered Index, let me show you the Execution plan of the Query. Below statement will select all the records from Employee1 table

Filtered Indexes in SQL Server 2

Create Non Clustered Index in SQL Server

Before we start creating the Filtered Index, let me create a basic Non Clustered Index on the Sales Amount column. We already explained the creation of the Non Clustered Index in our previous article. So, please refer to the same.

The below statement will create a SQL Server Non Clustered Index on Sales Amount Column present in Employee2 table

-- Create Non Clustered Indexes in SQL Server
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 Index (Employee 1) and With Non Clustered Index (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 Index

Filtered Indexes in SQL Server 4

Create a Filtered Index

In this example, we will show you how to add Filtered Indexes to an existing table. And the syntax is:

-- Create Non Clustered Indexes in SQL Server
CREATE NONCLUSTERED INDEX Index_Name
ON Table_Name (Column_Name(s) ASC/DESC)
WHERE Some_Condition

The below statement will create a Filtered Index in Sql Server on Sales Amount Column present in Employee 3 table

-- Create Non Clustered Indexes in SQL Server
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 Index (Employee 1), With Non Clustered Index (Employee 2), and With Filtered Index. Below statement will select Customer Key, and Sales Amount from Employee1, Employee2, and Employee3 table

Filtered Indexes in SQL Server 6

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 the indexes we created until now.

Filtered Indexes in SQL Server 7

Create Composite Filtered Index in SQL Server

SQL allows you to create a Filtered index on multiple columns called composite indexes. So, you can point the same index to Multiple columns. In this example, we are adding Filtered indexes on 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 Filtered Index?

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.

Filtered Indexes in SQL Server 15

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 on which you want to create a Filtered Index, and Right-click on the Indexes folder will open the context menu. Next, please select New Index and then select the Non-Clustered Index.. option

Filtered Indexes in SQL Server 8

Once you choose the Non-Clustered Index.. option, a new window called New Index will open.

Filtered Indexes in SQL Server 9

Please change the Index Name as per your requirements. Click on the Add button to select columns

Filtered Indexes in SQL Server 10

Next, select the column name on which you want to specify the Filtered Index. For now, we are choosing the Yearly Income column.

Filtered Indexes in SQL Server 11

Please change the sort order.

Filtered Indexes in SQL Server 12

Next, go to the Filter page and write your filter condition

Filtered Indexes in SQL Server 13

Delete Filtered Indexes in SQL Server

Please use the DROP INDEX statement to delete or drop Non Clustered Filtered Indexes in SQL Server

-- DROP Filtered Index in SQL Server

DROP INDEX Employee3.IX_Employee3_SalesAmount
Messages
--------
Command(s) completed successfully.

Use SSMS to Delete Filtered Index

To delete filtered Index in SQL Server, select the table on which your Filtered Index present. Next, expand the Indexes folder, and right-click on the key name, and choose the Delete option

Filtered Indexes in SQL Server 17

Once you choose the Delete option, Delete Object window will open. Click OK to delete the Index.

Filtered Indexes in SQL Server 18