Filtered Indexes in SQL

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

  • SQL Filtered Indexes will significantly improve the query performance because it is smaller than the Non-Clustered (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 them.

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

employee Records 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

Create Filtered Indexes in SQL Server 2

Create Non Clustered Index in SQL Server

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 SQL Server Non Clustered Index on Sales Amount Column present in 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.

Query Cost Relative to the batch 95% 4

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 in Sql Server on Sales Amount Column present in 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. 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 of them that we created until now.

View in Object Explorer  7

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 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 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.

EXECUTE SP_HELPINDEX Table_Name 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, and Right-click on the folder will open the context menu. Next, please select New and then select the Non-Clustered.. option

Filtered Indexes in SQL Server 8

Once you choose the option, a new window will open.

New Window to Create 9

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

Add name to it 10

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

Select the Column name 11

Please change the sort order.

Change the Sort order 12

Next, go to the Filter page and write your condition

Expression window 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 INDEX Employee3.IX_Employee3_SalesAmount
Messages
--------
Command(s) completed successfully.

Use SSMS to Delete

To delete, 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

Delete Filtered Indexes in SQL Server 17

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

Click the Ok button 18