Covering Indexes And Filtered Indexes In SQL

Hello learners. This article describes covering indexes and filtered indexes. I would like to tell you that these types of indexes can only be created as NON-CLUSTERED.

Before I proceed, I would like to tell you the difference between Key Columns and Non-Key Columns. Many of us know about this huge difference, but some of us may not. So, in order to have a better understanding, please keep this in mind. I will explain the difference using a simple example of creating indexes.

  1. Create NonClustered Index Ix_NC_Test  
  2. On Test(Col1)  
  3. Include(Col2,Col3)  

Here, Col1 is the Key Column, the column which is used while creating Indexes; the other Columns in the included clauses are Non-Key columns in case of Non-Clustered Indexes. You can also learn this as the Primary Columns (Key Columns) and Secondary Columns (Columns in Include Clause).

  1. Create Clustered Index Ix_C_Test  
  2. On Test(Col1,Col2,Col3)  

Here, columns 1, 2, and 3 all are Key Columns. Suppose the table has Col4 and 5 as well, they will be called as Non-Key Columns. Let us see another example.

  1. Create Clustered Index Ix_C_Test  
  2. On Test(Col1)  

Here, Col 1 is the Key Column, and Col 2, 3, 4, and 5 are Non-Key Columns.

So, let’s understand what Covering Indexes are, how they help in query optimization, and how they can be created. We all know Indexes help us to fetch data more quickly but only when carefully created. As it is just not a cake walk, there are many things to consider before creating indexes, like which columns should be included (those columns which are generally used as a predicate or in condition), whether to create a Clustered or Non-Clustered Index, which columns to include as Key Columns and which not.

But generally, indexes are created with a few columns as the more indexes, the more size it will take. When we create indexes on a table and run the query, the SQL Engine scans or seeks the table using the Indexes created on the table. If, in case,  certain columns that need to be fetched are not included in the Indexes, then the SQL Engine needs to perform a Key Lookup which actually is fetching the associated records after scanning or seeking the Index which in turn add one more operation.

What if I say that you can create an Index using all the required columns you generally use in the predicates; or fetching columns? This will greatly enhance the performance as the SQL Server does not need to perform Key LookUp for the Non-Key Values which it does when it does not find such columns in Key Columns. So, let’s understand Covered Indexes with some examples.

Covering And Filtered Indexes In SQL 

Inserting Test_CI with some random records.

Covering And Filtered Indexes In SQL 

I have to break/cancel the query in between as it was taking time, so the total records we have now are 85098. Now, we will create a NonClustered Index on ID Column.

Covering And Filtered Indexes In SQL 

We will now run the select query. Look at the execution plan of the query and Operator Cost and I/O Cost as well.

Covering And Filtered Indexes In SQL 

DBCC FREEPROCCACHE command is used to free the plan cache. Now, we will include the email column in our index obviously after dropping the existing Index.

Covering And Filtered Indexes In SQL 

Now look at the execution plan for the same query.

Covering And Filtered Indexes In SQL 

You will see no big difference just in the I/O Cost operator that is also very marginal. Now we will do some magic and create a COVERING INDEX, that means we will include all Key and Non-Key columns in our index and see the difference in the performance of the query.

Covering And Filtered Indexes In SQL 

Now we will run our query and see the execution plan. Here we go.

Covering And Filtered Indexes In SQL 

The SQL Engine does an Index Seek instead of Table Scan and has a look at the I/O Cost and Operator Cost. This is where a Covering Index is very useful. Yes, indexes are very useful as stated earlier if made “CAREFULLY”.

Now, I will show how FILTERED INDEXES can be extremely useful in query optimization. Here, we are dropping the existing index and create an index with a filter. These types of indexes are extremely useful in the case where you know what would be the search criteria, if the search criteria are predominantly of the same type for almost all the users then you can take benefit of the FILTERED INDEX. Please refer to the I/O and Operator Cost for the very first query we ran, keep that in mind and now we will create a FILTERED INDEX on the table.

Covering And Filtered Indexes In SQL 

Now, we are going to run the query and will see more magic around the corner.

Covering And Filtered Indexes In SQL 

Please take a look at the I/O Cost and Operator Cost -- even less than the Covering Index. So this is all from my side regarding the Covering Indexes and Filtered Indexes as far as how to create them and how extremely useful they are when made properly.

In case you have any feedback please provide that, I will be glad to take it and improve . Thank you so much for taking the time to read this article. Happy Learning!

You can also read more articles on SQL Server here,