For some strange reason, covering indexes are not used or understood as much as they should.

To quote Microsoft… “A Covering Index is a nonclustered index built upon all of the columns required to satisfy a SQL Server query, both in the selection criteria and in the WHERE clause”.

Lets look at it from a technical perspective.

We have a table:

CREATE TABLE dbo.testTable (
sysID		int 		IDENTITY(1,1)	PRIMARY KEY,
firstName 	varchar(64)	NOT NULL,
lastName 	varchar(64)	NOT NULL,
employeeID 	smallint	NOT NULL	UNIQUE NONCLUSTERED
)

As you can see above, for some other (we’ll assume good) reason we’ve put a primary key and hence a clustered index on the sysID. We’ve also put a unique nonclustered constraint on employeeID.

Consider the following query:

SELECT	firstName
FROM	testTable
WHERE	employeeID = 12345

When this query is executed, the unique nonclustered index is searched for the employeeID value of 12345. Once found, the nonclustered index will return a pointer to the actual row of the table. At this point, the row is looked up and the firstName is retrieved.

With a covering index, all of the required data is included in the index. In this simple case, we could just create an ascending index on employeeID and firstName to create a covering index, but lets assume that the total amount of data in the index is more that 900 bytes per record (the limit for a index entry) but we need more than this limit. In this case we can use the INCLUDE clause.

We can use the following sql (drop the table first if it exists):

CREATE TABLE dbo.testTable (
sysID		int 		IDENTITY(1,1)	PRIMARY KEY,
firstName 	varchar(64)	NOT NULL,
lastName 	varchar(64)	NOT NULL,
employeeID 	smallint	NOT NULL
);
GO
 
CREATE UNIQUE NONCLUSTERED INDEX
	IX_dbo_testTable_employeeID_INC_firstName
ON		dbo.testTable(employeeID ASC)
INCLUDE	(firstName);
GO

Now, when the select statement earlier in this post is executed, all the data required to complete the query is “covered” in the index.

A covering index saves a table join to get the results. You can easily expect a 200% performance increase on most queries.