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.