Filtered indexes in SQL Server can dramatically increase the efficiency of queries against data in your database. They are a fantastic optimization tool you should understand and remember during the design and development of tables in your database!
In this brief tutorial, we’ll discuss what a filtered index is and how they can help our queries perform must faster.
We’ll discuss these topics:
- What is a SQL Server filtered index?
- How to create a filtered index
- Demonstrating a filtered index
- Tips, tricks, and links
Let’s get into it.
1. What is a SQL Server filtered index?
A filtered index is an index with a WHERE clause which allows you outline a subset of data to store in the index data structure, instead of the entire table content.
Filtered indexes come down to a simple principle: It will always be faster to look through a smaller set of data than a larger set of data. When SQL Server is looking through the data structure of an index to retrieve the data that a query is asking for, it will be able to traverse a smaller structure much faster than a larger structure.
One could say this truth is self-evident. As an analogy, it will take you much less time to look through a collection of 10 books than it will a collection of 10,000 books.
2. How to create a filtered index
Let’s create a table and add some data to it to understand the idea of a filtered index. We’ll create a Books table:
CREATE TABLE Books
(
BookID INT IDENTITY(10,1),
Author VARCHAR(30),
Title VARCHAR(50),
PublicationYear INT,
InStock BIT
)
INSERT INTO Books (Author, Title, PublicationYear, InStock) VALUES
('Fitzgerald', 'The Great Gatsby', 1925, 1),
('Dickens', 'Great Expectations', 1861, 1),
('Hemingway', 'The Old Man and the Sea', 1952, 0),
('Morrison', 'Song of Solomon', 1977, 1),
('Collins', 'The Hunger Games', 2008, 1),
('Austen', 'Sense and Sensibility', 1811, 1),
('Hugo', 'Les Misérables', 1862, 1),
('Bradbury', 'Fahrenheit 451', 1953, 0)
Let’s think about a query that we might want to run periodically against this data. Maybe we want to run a query that gathers details about the books that are out of stock in our bookstore. The query would look like the following:
SELECT Author, Title FROM Books WHERE InStock = 0
We can create a filtered index to make the retrieval of this specific data very quick. We’ll start with the creation of a nonclustered index:
CREATE INDEX idx_OutOfStockBooks ON Books(Author, Title) INCLUDE (InStock) .....
So far in this statement, we’ve outlined that our index key is the Author and Title columns. We also chose to INCLUDE the InStock column.
(Learn more about nonclustered indexes and INCLUDE columns)
Then all we do is append a WHERE clause to establish a filter on the index, like so:
CREATE INDEX idx_OutOfStockBooks ON Books(Author, Title) INCLUDEÂ (InStock)Â WHERE InStock = 0
Here’s why this index is so helpful. It contains the Author, Title, and InStock values for only the out of stock books, which is a subset of our data. It does not contain details for all the books in our inventory.
In the data structure that is created behind the scenes for this index (the B-Tree), the only book data it will store is the book data for only the out-of-stock books (InStock = 0). In our case, that’s only 2 books!
If we want to gather details for just out-of-stock books, it will likely be much faster to look through this index than any other index or to perform a full table scan.
3. Demonstrating a filtered index
To see what SQL Server does behind the scenes when executing a query, we need to enable the “Include Actual Execution Plan” button in SQL Server Management Studio. The button is here:
After we create our index and enable that button, we can run our query and see the Execution Plan:
Fantastic! Since we don’t have any other indexes on our table, SQL Server faced the choice of either doing a full table scan to retrieve the data or traversing through our filtered index. Looks like the index is the fastest way to get the data!
4. Tips, tricks, and links
Here is a list of a few tips and tricks you should know when it comes to creating and working with SQL Server filtered indexes:
Tip # 1: The idea of filtering indexes only applies to nonclustered indexes
The idea of filtering indexes only applies to nonclustered indexes. SQL Server won’t let you apply filters to a clustered index.
In our CREATE INDEX statement above, we chose to not specify if the index was clustered or nonclustered. When that’s the case, the index defaults to being nonclustered.
Tip # 2: Filtered indexes work best on columns that contain only a few distinct values
Ideally, you want to establish a filter using a column that doesn’t contain many distinct values so that it’s easier for SQL Server to maintain the data in the index.
In our case, the InStock column will only be either a 1 or a 0, which is a very small number of possible values.
Links:
You’ll probably find the following FREE Ebook helpful:
FREE Ebook on Sql Server Indexes!
This FREE Ebook contains absolutely everything you need to know about indexes in Microsoft SQL Server, including:
- Clustered Indexes
- Nonclustered Indexes
- Unique Indexes
- Understanding the B-tree
This ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional.
In it, we talk in great detail about nonclustered indexes. It also discusses a downside to nonclustered indexes you should consider before adding them to your tables.
Download the ebook today!
Next Steps:
Leave a comment if you found this tutorial helpful!
As mentioned earlier, you really need to understand how nonclustered indexes work to fully understand the idea of filtered indexes. Check out the full beginner-friendly tutorial to learn more:
Nonclustered Index in SQL Server: A Beginner’s Guide
While we’re at it, you should understand how clustered indexes work, too. Check it out:
SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner
Indexes are monumental towards writing efficient queries against our databases. Make sure you know how they work and how to create them!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
