Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 236

Non-ClusteredIndexes
Non-clustered indexes don't dictate the storage as do clustered indexes. Also, a table can have
several non-clustered indexes, whereas it can only have one clustered index. If you've applied the rules
listed above for clustered indexes and find that the index you want to create shouldn't be a clustered
index, then your only choice is to make it non-clustered.

All the design rules given at the beginning of this section apply to non-clustered indexes. Keep them
small, use non-variable length columns whenever possible, and use the most selective columns you can.

For non-clustered indexes, the bottom of the index structure are pointers to the data pages. Therefore,
when traversing a non-clustered index, SQL Server will have to make one extra operation to retrieve
the data.

Covering Indexes
A covering index includes all of the columns referenced in the SELECT, JOIN, and WHERE clauses of a
query. Thus, the index contains all the data and SQL Serverdoesn'thavetogotothedatapagestogetit.
It's all in the index.

Covering indexes have to be used carefully. If they have too many columns, they can actually increase
I/O and negatively impact performance. A couple of guidelines for covering indexes are listed below:

First, make sure that the query you're creating for the covering index is frequently run. If it's not
then don't create a covering index.

❑

Don't add too many columns to an existing index to get a covering index.

❑

The covering index must include all columns found in the query. That includes the SELECT,
JOIN, and WHERE clauses of the query.

❑

Include Indexes
Include indexes are new to SQL Server 2005. They provide an additional method of creating covering
indexes. An example statement that creates an include index from the AdventureWorks database is:

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (City, StateProvinceID)

This works by placing the columns listed in the INCLUDE clause on the leaf level nodes of the index. All
the other nodes at the non-leaf level won't have those columns. Also, those columns won't be used when
calculating the number of index key columns or index key size.

This is really the way to create covering indexes in SQL Server 2005 because these non-key columns won't
take up unnecessary space in the index key. However, when data is retrieved it can still be done from the
index, and the data won't have to be read from the data pages.