Index Architecture and Behavior - SQL Server Query Performance Tuning - page 129

same logical key values of the clustered index key, even though the data rows have physically moved to a different
location. In the case of a data page split, the row locators of the nonclustered indexes need not be updated. This is an
important point since tables often have a large number of nonclustered indexes.
Things don't work the same way for heap tables. While page splits in a heap are not a common occurence, and
when heaps do split, they don't rearrange locations in the same way as clustered indexes, you can have rows move
in a heap, usually due to updates causing the heap to not fit on it's current page. Anything that causes the location of
rows to be moved in a heap results in a forwarding record being placed into the original location pointing to that new
location, necessitating even more I/O activity.

■ Note page splits and their effect on performance are explained in more detail in Chapter 13.

Clustered Index Recommendations
The relationship between a clustered index and a nonclustered index imposes some considerations on the clustered
index, which are explained in the sections that follow.

Create the Clustered Index First
Since all nonclustered indexes hold clustered index keys within their index rows, the order of nonclustered and
clustered index creation is important. For example, if the nonclustered indexes are built before the clustered index is
created, then the nonclustered index row locator will contain a pointer to the corresponding RID of the table. Creating
the clustered index later will modify all the nonclustered indexes to contain clustered index keys as the new row
locator value. This effectively rebuilds all the nonclustered indexes.
For the best performance, I recommend you create the clustered index before you create any nonclustered index.
This allows the nonclustered indexes to have their row locator set to the clustered index keys at the time of creation.
This does not have any effect on the final performance, but rebuilding the indexes may be quite a large job.
As part of creating the clustered index first, I also suggest you design the tables in your database around the clustered
index. It should be the first index created because you should be storing your data as a clustered index by default.

Keep Clustered Indexes Narrow
Since all nonclustered indexes hold the clustered keys as their row locator, for the best performance keep the overall
byte size of the clustered index as small as possible. If you create a wide clustered index, say CHAR(500) , in addition to
having fewer rows per page in the cluster, this will add 500 bytes to every nonclustered index. Thus, keep the number
of columns in the clustered index to a minimum, and carefully consider the byte size of each column to be included in
the clustered index. A column of the integer data type often makes a good candidate for a clustered index, whereas a
string data type column will be a less-than-optimal choice.
To understand the effect of a wide clustered index on a nonclustered index, consider this example. Create a small
test table with a clustered index and a nonclustered index.

IF (SELECT OBJECT_ID('Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 INT);