Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 223

Table 8-5: Row Layout Details

Information Handle Size Purpose

Status Bits A StatusA 1 byte Internal. Ignore.

Status Bits B StatusB 1 byte Internal. Ignore.

Fixed-length size FS 2 bytes Total storage occupied by all the

fixed-length columns.

Fixed-length data FD FS - 4 All the fixed-length data.

Number of columns NC 2 bytes Number of columns in the table.

NULL bitmap IsNull Ceiling (NC / 8) 1 byte for each column. A 1 indicates that

the column is NULL.

Number of
variable-length
columns

VC 2 bytes Indicates the number of variable length

columns in the table.

Variable column
offset array

VO 2 * VC Each variable-length column contains an

offset value. This is a 2-byte entry which
indicates the position in the data row
where the column ends.

Variable-length data VD VO[VC] − All the variable-length column data.

+ +

(FS 4 Ceiling (NC /

+

8) 2*VC)

The row offset array indicates the logical order of rows on a page. For tables that have a clustered index,
the logical order is dictated by the clustered key. Note that SQL Server will not physically store the rows
on the page in the order of the key. However, the first slot in the offset array will refer to the first row
in the key order, the second slot will refer to the second row, and so on. For tables that don't have a
clustered index, the logical order is the order in which the rows are inserted.

ExtentPages
You may have heard or read the term extent pages. This terminology is somewhat confusing. Extent
pages aren't some special type of data page. Instead, they're simply eight logically contiguous data
pages. When SQL Server allocates storage to a table, it doesn't allocate just one page. Instead, it allocates
eight. Likewise, when SQL Server reads a data page it doesn't read just one, it reads eight. There are two
types of extents:

❑ Mixed extents: These are shared by up to eight tables.

❑ Uniform extents: These are owned by a single table.

A new table or index is allocated pages from mixed extents. However, when the table grows to require
eight pages for itself, it shifts to use uniform extents. Also, once a table begins using uniform extents it
willnevergobacktousingmixedextents.