Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 359

Heaps
A table without a clustered index is known as a heap because there is no enforced order to the data; all
inserts go to the end of the table. This makes inserts very fast, and heaps are often used in data loading
scenarios where the indexes are re-applied afterwards. They are also good for forward-only scenarios
like auditing, where you're always writing to the end of the table and not deleting anything.

Small tables can often benefit from being a heap where the data is so small that the optimizer always
chooses a table scan rather than an index. In most heap scenarios, however, you should apply a non-
clustered index to the heap or every access to the table would need to be a table scan.

Nonclustered Indexes
The non-leaf levels of a nonclustered index are exactly the same as those of a clustered index. Where the
b-trees differ is in what they contain at the leaf level. When you traverse a b-tree in a clustered index,
the leaf level is the data itself. When you traverse a non-clustered tree, the leaf level contains a pointer
to the data. This pointer can take two forms depending on the underlying table structure. If the table is a
heap, the pointer will be a Row IDentifier (RID), which is an 8-byte value representing the file id, page,
and slot number.

If the table has a clustered index, then the pointer is actually the clustered index key. When you tra-
verse the b-tree the leaf level will contain the non-clustered key and the clustered key. When SQL Server
has the clustered index key, it can then traverse the clustered index to get the rest of the data. This extra
b-tree traversal means that non-clustered indexes are better when your data has high selectivity.

CoveringIndexes
A covering index means that all the columns required to provide the result set of a query are found
at the leaf-level of a non-clustered index. This means all the columns in the SELECT statement and the
WHERE clause (if there is one). For example, if I had a clustered index on dbo.people.personID and
a non-clustered index on dbo.people.lastname , a query like select personID , lastname from peo-
ple would be covered by the non-clustered index. All the data is available at the leaf level of the index
without having to go to the underlying clustered table.

If you added firstname to the SELECT statement or to a WHERE clause, the query wouldn't be cov-
ered, and a trip to the clustered index would be required. You could drop the non-clustered index and
recreate it on lastname , firstname to get a covering index, but you have to balance the overall system
requirements with the need to tune specific important queries. Trying to cover every query would be
detrimental to the performance of most systems and shouldn't even be necessary.

IncludeColumns
This is a great feature introduced with SQL Server 2005 that allows you to include columns at the
leaf-level of an index without them being part of the index tree. The sole purpose of this is to have more
covered queries and it provides a number of additional benefits as well:

❑ Index keys have a limitation of 900 bytes and 16 columns. Included columns don't count toward

this limitation so they provide an easy workaround to get more data into an index.