What is Filtered Index in SQL Server

 

The Filtered Index is a filtered nonclustered index. For example, you have 100,000 records in a table. And some of your queries are only interested in 20 thousand of these 100 thousand records. If you create nonclustered index in the table for these queries, this index will have 100 thousand records. But, since the query interested in only 20 thousand of 100 thousand records, it would be unnecessary to create an index of 100 thousand records. Here we see the filtered index. We can create an index for the recordset that the query will use, so that the query can run more efficiently.

Advantages of Filtered Index:

  • Reduced storage usage with fewer records
  • Because there are fewer records, the execution plan will be better quality. You can find details about the Execution Plan in the article “What is Execution Plan on SQL Server“.

Also, the filtered index will have filtered statistic. Filtered statistics will be more accurate than full-table statistics. You can find more detailed information in my article “Statistics Concept and Performance Effect on SQL Server” on statistics.

In summary, fewer records will be searched using a higher quality execution plan and a more accurate statistic. So there will be a serious performance increase.

  • Maintenance will be easier as there are fewer records.

Let’s continue with an example of our article.

In our test database, we create a new table with the help of the following script and insert several records into this table.

As you can see in the script, we’ve added 10 records. There are some values ​​in 3 of these 10 records. Other records have been inserted as null. Suppose we need to create index in Name and Surname columns. If we create nonclustered index, the number of records in this index will be as much as the number of records in the table. For more effective use, let’s create a filtered index that contains only non-null records for the Name and Surname columns.

In order to make a comparison, let’s create a nonclustered index without a filter.

Let’s check the number of records in nonclustered indexes in our table with the help of the following script.

As you can see, there are 10 records in the normal nonclustered index named Name_Surname, and there are 3 records in the Filtered Index.

You can delete the filtered index using the following script.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *