Pages and Extents

The fundamental unit of data storage in Microsoft® SQL Server™ is the page. In SQL Server version 7.0, the size of pages is 8 KB. This means SQL Server 7.0 databases have 128 pages per megabyte.

The start of each page is a 96 byte header used to store system information such as the type of page, the amount of free space on the page, and the object ID of the object owning the page.

There are six types of pages in the data files of a SQL Server 7.0 database.

Page type Contents
Data Data rows with all data except text, ntext, and image data
Index Index entries
Text/Image text, ntext, and image data
Global Allocation Map Information about allocated extents
Page Free Space Information about free space available on pages
Index Allocation Map Information about extents used by a table or index

Log files do not contain pages, they contain a series of log records.

Data pages contain all the data in data rows except text, ntext, and image data, which is stored in separate pages. Data rows are placed serially on the page starting immediately after the header. A row offset table starts at the end of the page. The row offset table contains one entry for each row on the page and each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

Rows cannot span pages in SQL Server. In SQL Server 7.0, the maximum amount of data contained in a single row is 8060 bytes, not including text, ntext, and image data.

Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB. This means SQL Server 7.0 databases have 16 extents per MB.

To make its space allocation efficient, SQL Server 7.0 does not allocate entire extents to tables with small amounts of data. SQL Server 7.0 has two types of extents:

A new table or index is allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it is switched to uniform extents.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.