Database Reference
In-Depth Information
Figure 32-6.
Range Index
Even though a Bw-Tree looks very similar to a B-Tree, there is one conceptual difference: The leaf level of an
on-disk B-Tree index consists of separate index rows for each data row in the index. If multiple data rows have the
same index key value, each row would have an individual index row stored.
Alternatively, in-memory range indexes store one index row (pointer), to the row chain that includes all of the
data rows that have the same key value. Only one index row (pointer) per key value is stored in the index. You can see
this in Figure
32-4
, where the leaf-level of the index has single rows for the key values of
Ann
and
Nancy,
even though
the row chain includes more than one data row for each value.
Every time SQL Server needs to change a leaf-level index page; it creates one or two
delta
records that represent
the changes.
INSERT
and
DELETE
operations generate a single insert or delete delta record, while an
UPDATE
operation
generates two delta records, deleting old and inserting new values. Delta records create a chain of memory pointers
with the last pointer to the actual index page. SQL Server also replaces a pointer in the mapping table with the address
of the first delta record in the chain.
Figure
32-7
shows an example of a leaf-level page and delta records if the following actions occurred in the
sequence: R1 index row is updated, R2 row is deleted, and R3 row is inserted.
