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

Figure 8-17. Execution plan against a heap
As you can see, the index was used in a Seek operation. But because the data is stored separately from the
nonclustered index, an additional operation, the RID Lookup operation, is required in order to retrieve the data,
which is then joined back to the information from the Index Seek operation through a Nested Loop operation. This
is a classic example of what is known as a lookup , in this case an RID lookup, which is explained in more detail in the
“Defining the Lookup” section. A similar query run against a table with a clustered index in place will look like this:
SELECT d.DepartmentID,
d.ModifiedDate
FROM HumanResources.Department AS d
WHERE d.DepartmentID = 10 ;
Figure 8-18 shows this execution plan returned.
Figure 8-18. Execution plan with a clustered index
Although the primary key is used in the same way as the previous query, this time it's against a clustered index.
As you now know, this means the data is stored with the index, so the additional column doesn't require a lookup
operation to get the data. Everything is returned by the simple clustered Index Seek operation.
To navigate from a nonclustered index row to a data row, this relationship between the two index types requires
an additional indirection for navigating the B-tree structure of the clustered index. Without the clustered index, the
row locator of the nonclustered index would be able to navigate directly from the nonclustered index row to the data
row in the base table. The presence of the clustered index causes the navigation from the nonclustered index row to
the data row to go through the B-tree structure of the clustered index, since the new row locator value points to the
clustered index key.
On the other hand, consider inserting an intermediate row in the clustered index key order or expanding the
content of an intermediate row. For example, imagine a clustered index table containing four rows per page, with
clustered index column values of 1 , 2 , 4 , and 5 . Adding a new row in the table with the clustered index value 3 will
require space in the page between values 2 and 4 . If enough space is not available in that position, a page split will
occur on the data page (or clustered index leaf page). Even though the data page split will cause relocation of the data
rows, the nonclustered index row locator values need not be updated. These row locators continue to point to the