Understanding Filtered Indexes in SQL Server | Performance Tuning | SQL Training

In this tutorial we look at filtered indexes in SQL server. We create them with the INCLUDE clause and see when they are and are not used, along with seeing how they can help with performance tuning.

Contents

00:00 Intro
00:27 What is a Filtered Index?
02:17 Analyse Query Using Execution Plan
03:35 Create Filtered Index
04:14 Filtered vs Non Filtered Index performance
05:20 Filtered Index not being used - why?
06:44 Create Filtered Index with Include

What is a filtered index in SQL Server?

A filtered index is basically a trimmed down version of a standard Non-Clustered index. Some describe it as a lightweight index as it contains certain rows from a column. You can create Filtered Indexes using the following types of operators:

- Equals =
- Greater or Less Than
- Using IN
- Creating more than one filter in your WHERE

Filtered Index Benefits

The benefits of a filtered index would mean that there are reduced storage costs, because the index is smaller in size. Write operations are potentially less frequent, reading from the index would be quicker than a non filtered index because of its reduction in rows. As a database administrator this is great as your maintenance routines such as index rebuilds would be quicker, an end user wouldn't care about this but its certainly beneficial for DBAs.

Filtered Index in SQL Server Example

Imagine you have an index on gender for a list of employees, you could create an index just on 'F' (if this suited the majority of your queries). This would mean that your index would only contain rows where the Gender = 'F', so it could reduce the size of your index by 50%.

Downsides to filtered indexes

There are downsides to creating filtered indexes, unless you understand which query or queries you are targeting then there is a chance they may not be used - I give a great example of how in this tutorial.

Another potential downside is that you could end up doubling up on your indexes unintentionally. In the above example of creating an index on 'F', if you had an index on all genders and they were searched for equally then the filter index wouldn't be worth it. However, if you searched for 'F' 90% of the time then it would be very beneficial, so the lesson here is "understand what your filtered index is for, before creating it! No guess work!"

Filtered index with INCLUDE

This tutorial shows you how to create an index with an INCLUDE. The reason for this is that some queries may ask for more columns than the index can return - it can't satisfy the query, so we need to include further columns to ensure our index is being used. I cover this well in the tutorial.

Limitations of Filtered Indexes

Due to the design of filtered indexes you are unable to create indexes with:
- Date Functions, such as DATEADD
- On Computed columns
- Using expressions such as NOT IN and CASE

Filtered Indexes Best Practice

Best practice for SQL Server indexes would be as follows:
-Have a good naming convention. In this tutorial I use IX_tablename_columnnames_F to show its filtered
- Understand the query which you are targeting, do not create them hoping for them to be used – they most likely won’t be
- If possible, target most unique column values. This best practice with all SQL Server indexes
Brent Ozar:
https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes

Microsoft:
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver16

SQL Shack, Introduction to Filtered Indexes: https://www.sqlshack.com/introduction-to-sql-server-filtered-indexes

Видео Understanding Filtered Indexes in SQL Server | Performance Tuning | SQL Training канала The SQL Guy