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

Relationship with Nonclustered Indexes
There is an interesting relationship between a clustered index and the nonclustered indexes in SQL Server. An index
row of a nonclustered index contains a pointer to the corresponding data row of the table. This pointer is called a row
locator. The value of the row locator depends on whether the data pages are stored in a heap or on a clustered index.
For a nonclustered index, the row locator is a pointer to the row identifier ( RID) for the data row in a heap. For a table
with a clustered index, the row locator is the clustered index key value.
For example, say you have a heap table with no clustered index, as shown in Table 8-4 .

Table 8-4. Data Page for a Sample Table

RowID (Not a Real Column) c1 c2 c3

1 A1 A2 A3

2 B1 B2 B3

A nonclustered index on column c1 in a heap will cause the row locator for the index rows to contain a pointer
to the corresponding data row in the database table, as shown in Table 8-5 .

Table 8-5. Nonclustered Index Page with No Clustered Index

c1 Row Locator

Pointer to RID = 1

A1

Pointer to RID = 2 B1

On creating a clustered index on column c2 , the row locator values of the nonclustered index rows are changed.
The new value of the row locator will contain the clustered index key value, as shown in Table 8-6 .

Table 8-6. Nonclustered Index Page with a Clustered Index on c2

c1 Row Locator

A1 A2

B1 B2

To verify this dependency between a clustered and a nonclustered index, let's consider an example. In the
AdventureWorks2012 database, the table dbo.DatabaseLog contains no clustered index, just a nonclustered primary
key. If a query is run against it like the following, then the execution will look like Figure 8-17 .

SELECT dl.DatabaseLogID,
dl.PostTime
FROM dbo.DatabaseLog AS dl
WHERE dl.DatabaseLogID = 115;