how to turn non-clustered Index into covering indexs

Question

I googled covering index and found:

"A covering index is a special type of composite index, where all of the columns exist in the index."

I understand the main purpose is to make non-clustered index don't lookup clustered Index, and for SQL Server, we can use 'INCLUDE' columns when creating an index, so SQL Server adds them at the leaf level of the index. So there is no need to look up via cluster-index.

But image we have an Customers table(CustID, FirstName, City) that has a clustered Index on CustID.

if we create a non-clustered Index(called IX_FIRSTNAME) on FirstName column, and include this column as payload in the leaf node of the index and query as:

select FirstName from Customers where FirstName Like 'T*';

so in this case, there is no need to look up through clustered Index, so can IX_FIRSTNAME be considered as a covering index?

or it has to meet the requirement for all columns? and we need to create a non-clustered for all three columns to be a covering index?

Answer 1

There are two concepts here:

• clustered versus non-clustered indexes
• covering indexes

A covering index is one that can satisfy the where clause in your query. As you are likely to have more than one query running against your data, a given index may be "covering" for one query, but not for another.

In your example, IX_FIRSTNAME is a covering index for the query

select FirstName from Customers where FirstName Like 'T*';,

but not for the query

select FirstName from Customers where FirstName Like 'T*' and City Like 'London';.

A lot of performance optimization boils down to "understand your queries, especially the where clause, and design covering indexes". Creating indexes for every possible combination of columns is a bad idea as indexes do have a performance cost of their own.

The second concept is "clustered" versus "non-clustered" indexes. This is more of a physical concern - with a clustered index, the data is stored on disk in the order of the index. This makes it great for creating an index on the primary key of a table (if your primary key is an incrementing integer). In your example, you would create a clustered index on custid, which would be covering the following query:

select FirstName from Customers where custid = 12

It would also help joins (e.g. from customer to order).