Database Reference
In-Depth Information
Figure 1-5.
The data page structure
A 96-byte page header contains various pieces of information about a page, such as the object to which the page
belongs, the number of rows and amount of free space available on the page, links to the previous and next pages if
the page is in an index page chain, and so on.
Following the page header is the area where actual data is stored. This is followed by free space. Finally, there is
a slot array, which is a block of 2-byte entries indicating the offset at which the corresponding data rows begin on
the page.
The
slot array
indicates the logical order of the data rows on the page. If data on a page needs to be sorted in
the order of the index key, SQL Server does not physically sort the data rows on the page, but rather it populates the
slot array based on the index sort order. The slot 0 (rightmost in Figure
1-5
) stores the offset for the data row with the
lowest key value on the page; slot 1, the second lowest key value; and so forth.
■
Note
We will discuss indexes in greater detail in Chapter 2, “Internal Structure and access Patterns.”
SQL Server offers a rich set of the system data types that can be logically separated into two different groups:
fixed length and variable length. Fixed-length data types, such as
int
,
datetime
,
char
, and others always use the same
amount of storage space regardless of their value,
even when it is NULL
. For example, the
int
column always uses 4
bytes and an
nchar(10)
column always uses 20 bytes to store information.
In contrast, variable-length data types, such as
varchar
,
varbinary
, and a few others, use as much storage space
as required to store data plus two extra bytes. For example an
nvarchar(4000)
column would use only 12 bytes to
store a five-character string and, in most cases, 2 bytes to store a NULL value. We will discuss the case where
variable-length columns do not use storage space for NULL values later in this chapter.
Let's look at the structure of a data row, as shown in Figure
1-6
.
