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.

Create NonClustered Index Ix_NC_Test  
On Test(Col1)  
Include(Col2, Col3)

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

CREATE CLUSTERED INDEX Ix_C_Test 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 Non-Key Columns. Let us see another example.

Create Clustered Index Ix_C_Test 
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 cakewalk, 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 fetches 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 the Covered Indexes with some examples.

Inserting Test_CI with some random records.

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 the ID Column.

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

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.

Now look at the execution plan for the same query.

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

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

The SQL Engine does an Index Seek instead of a 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 creating 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.

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

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.

Articles on SQL Server