Covering index and its usage

What is a covering index

A covering index is an index which satisfies all the columns being used in a query without performing a further lookup either to the clustered index or to the base table. It contains all the columns whether they are used in SELECT, JOIN, WHERE, GROUP BY, or ORDER BY clause of the query. Covering index works like a pseudo-clustered index for the query. It improves the performance of the query by adding non-key columns in the index which help the query optimizer to remove any lookup which can be a key lookup or a RID lookup. A key lookup is performed in case the base table accommodates a clustered index otherwise a RID lookup is used by the query optimizer to fetch the non-key columns needed by the query. To know more about key lookup and RID lookup, click here.

The optimizer doesn’t need any further lookup in order to read non-key columns if the query is being served by a clustered index. Because the leaf node of the clustered index contains the actual table data which is sorted by the key columns of the index. Clustered index organizes the physical table in a B-Tree (balanced tree) structure and it does not claim separate space on the disk other than the actual table data rows space. But, we can have only one clustered index on a table.

Unlike clustered index, if the query is being served by a nonclustered index and the index does not accommodate other non-key columns needed by the query, the optimizer will perform a lookup to extract the non-key columns during execution of the query. The leaf node of the nonclustered index points to the base table using a row locator which can be a pointer to the index key of a clustered index if table accommodates on a clustered index, else it points to the row id of the base table in case the table is a heap table. The nonclustered index claims separate space on the disk and creates a separate structure isolated from the actual table.  We can have 999 nonclustered indexes on one table but remember that each nonclustered index creates an overhead to the table especially in the case of the DML statements. In understanding where to prefer clustered index and where to prefer nonclustered index,

In understanding where to prefer clustered index and where to prefer nonclustered index, click here.

Benefits of covering index

The covering index helps us to accommodate non-key columns at the leaf node of a nonclustered index so that the optimizer can extract these columns from the index itself without performing a lookup to the base table. SQL Server provides INCLUDE keyword to add these non-key columns with the nonclustered index. Let’s look at the benefits of the covering index which are as below:

1. The limit of index key bytes can be bypassed by moving the wide non-key columns in INCLUDE clause.
2. We can use those columns whose data types are not supported by the index key columns.

We know that the maximum number of bytes in a nonclustered index can be up to 1700 bytes. You can see maximum size specification for SQL Server here. If the index key width is going beyond the maximum bytes limit, we can simply move the wide key columns to the INCLUDE clause of the nonclustered index key. The columns used inside the INCLUDE clause of a nonclustered index are not used to sort the index key data.

Create covering index

Let’s demonstrate the use of covering index and how it can remove the bookmark lookups from the query plan. To demonstrate, have a look on the HumanResources.Employee table of AdventureWorks Database. This table accommodates a nonclustered index AK_Employee_LoginID on LoginID column which is as below:

We can see that the index AK_Employee_LoginID is created on LoginID column which is the key column of the index also. Now, execute the below query and have a look at the query execution plan:

SELECT LoginID, JobTitle FROM HumanResources.Employee WHERE LoginID = 'adventure-works\ken0'

Query execution plan

Now, to remove the lookup from the above query execution plan, let’s add the JobTitle column needed by the query in SELECT clause as a non-key column in the index AK_Employee_LoginID as below:

CREATE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee(LoginID)
INCLUDE (JobTitle) 
WITH (DROP_EXISTING = ON)

Now, reexecute the above query and have a look at the query execution plan which is as below:

Thank you for the reading. Please rate share and input your suggestions.