SQL Server - Covering Index

SQL Server - Covering Index

In simple words, an index which contains all the data needed to satisfy a query is known as a covering index.

A covering index is always a non-clustered index which is built on all the columns required to satisfy a query without accessing the base table. For example if you write some query and the query encounters an index which completely satisfies the query and its not needed for the query to refer to the underlying data table, then the index can be consider as a covering index.

Important points related to covering indexes:

1. Using covering index, number of logical reads can be reduced for a query.

2. Covering indexes can also help resolve blocking and deadlocks.

3.

Select id, name, address
From employees
Where id = ‘9’;

For example, above query needs to access only 3 columns from the many columns in the employees table. If an index is created with all these three columns (id, name, address), the query will be much faster. You need to make sure that the query is run very frequently before you create a covering index for it, because if covering index is created for a query that is seldom or never run then only the space in the database is wasted.

Lets look at a very simple example of covering index:

a. Lets create a table my_numbers:

CREATE TABLE my_numbers
(
col1 varchar(255),
col2 varchar(255)
);

b. Insert some 10,000 rows into this newly created table:

DECLARE @var_first AS INT
SET @var_first = 1
DECLARE @counter AS INT
SET @counter = 1
DECLARE @finish AS INT
SET @finish = 10000

BEGIN TRANSACTION
WHILE(@var_first <= @finish)
BEGIN
INSERT INTO my_numbers VALUES(@var_first, @var_first+1 )
SET @var_first += @counter
END
COMMIT TRANSACTION

c. Make sure everything is fine until now:

Select * from my_numbers

d. Now lets run the below query to find records between 8001 and 8030:

SET STATISTICS IO ON
select col1,col2 from my_numbers
where col1 between '8001' and '8030'

e. Check the stats for the above run query:

(33 row(s) affected)
Table 'my_numbers'. Scan count 1, logical reads 30, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.

As seen above, logical reads are 30.

f. Create a clustered covering index on my_numbers:

CREATE NONCLUSTERED INDEX numbers_CoveringIndex
ON my_numbers (col1, col2);

g. Below is the query you can use to see all the indexes on a table:

select * from sys.indexes
where object_id = 
(select object_id from sys.objects where name = 'my_numbers')

h. Run the same query again (as in step d) after creating the covering index and check the stats:

select col1,col2 from my_numbers
where col1 between '8001' and '8030'

i. Check the stats for the above run query:

(33 row(s) affected)
Table 'my_numbers'. Scan count 1, logical reads 3, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.

Now observe that the logical reads have decreased drastically from 30 to 3 after we created the covering index.