Covering Index versus Clustered Index (Database Index)

Question

Hi,

I'm working on a database system and it's indexes, but I'm having a really hard time seing the clear difference between a covering index and a clustered index.

I've googled my way around but hasn't got a clear cut answer on:

1. What is the differences between the two types of indexes
2. When do I use Covering index and when do I use Clustered index.

I hope someone can explain it to me in a almost children-like answer :-)

Sincerely Mestika

By the way, I'm using IBM DB2 version 9.7

Answer 1

I cannot speak to DB2, but the following applies to SQL Server.

When all of the required columns are part of the index the index is called a a "covering index". SQL Server 2005 introduced this type of index by allowing you to have "included columns" in the index. This allows you to include additional columns in the index over the 16 column limit or columns that would be too large to include.

While you can only have one clustered index per table, you can have up to 249 non-clustered indexes per table.

By having a covering index available to satisfy a query, SQL Server won't need to go back to the clustered index to retrieve the rest of the data required by the query.

Randy