Database Reference
In-Depth Information
Figure 2-2.
Forwarding pointers
There are two main reasons why forwarding pointers are used. First, they prevent updates of nonclustered index
keys, which reference the row.
■
Note
We will talk about nonclustered index structure in more detail later in the chapter.
In addition, forwarding pointers helps minimize the number of duplicated reads; that is, the situation when a
single row is read multiple times during the table scan.
Let's look at Figure
2-2
as an example of this and assume that SQL Server scans the pages in left-to-right order.
Let's further assume that the row in page 3 was modified after the page was read at the time when SQL Server reads
page 4. The new version of the row would be moved to page 5, which has yet to be processed. Without forwarding
pointers, SQL Server would not know that the old version of the row had already been read, and it would read it again
during the page 5 scan. With forwarding pointers, SQL Server would ignore the forwarded rows.
Although forwarding pointers help minimize duplicated reads, they introduce additional read operations at
the same time. SQL Server follows the forwarding pointers and reads the new versions of the rows at the time it
encounters them. That behavior can introduce an excessive number of I/O operations.
Let's look at the following example, create the table, and insert three rows with the code shown in Listing 2-4.
Listing 2-4.
Forwarding pointers and IO: Table creation and three rows insert
create table dbo.ForwardingPointers
(
ID int not null,
Val varchar(8000) null
);
insert into dbo.ForwardingPointers(ID,Val)
values
(1,null),
(2,replicate('2',7800)),
(3,null);
