Database Reference
In-Depth Information
Figure 32-9.
Data modifications and concurrency: Step 2
■
SQL Server uses the same approach with
InterlockedCompareExchange
in cases when the pointers chain
needs to be preserved. For example, when it creates another version of a row during an update.
Note
The internal and leaf pages of range indexes consist of two areas: a header and data. The header area includes
information about the page such as:
PID
: The position (offset) in the mapping table.
Page Type
: The type of the page, such as leaf, internal, delta, or special.
Right Page PID
: The position (offset) of the next page in the mapping table.
Height
: The number of levels from the current page to the leaf level of the index.
The
Number of key values
(index rows) stored on the page.
Delta records statistics
: Includes the number of delta records and space used by the
delta key values.
The
Max value of a key
on the page.
The data area of the page includes either two or three arrays depending on the index keys data types. The arrays are:
Values
: An array of 8-byte pointers. Internal pages in the index store the PID of
next-level pages. Leaf-level pages store pointers to the first row in the chain of rows with
the corresponding key value. It is worth noting that, even though PID requires 4 bytes to
store a value, SQL Server uses 8-byte elements to preserve the same page structure between
internal and leaf pages.
Keys
: An array of key values stored on the page.
Offsets
: An array of two-byte offsets where individual key values in key arrays start. Offsets
are stored only if keys have variable-length data.
Delta records, in a nutshell, are one-record index data pages. The structure of delta data pages is similar to the
structure of internal and leaf pages. However, instead of arrays of values and keys, delta data pages store operation
code (insert or delete) and a single key value and pointer to the data row.
