Pages and Extents
The fundamental unit of data storage in Microsoft® SQL Server™ is the
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.
The table shows eight types of pages in the data files of a SQL Server 2000 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, Secondary 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. |
| Bulk Changed Map | Information about extents modified by bulk operations since the last BACKUP LOG statement. |
| Differential Changed Map | Information about extents that have changed since the last BACKUP DATABASE statement. |
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 2000, the maximum amount of data contained in a single row is 8060 bytes, not including text, ntext, and image data.
To make its space allocation efficient, SQL Server 2000 does not allocate entire extents to tables with small amounts of data. SQL Server 2000 has two types of extents:
- Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
- Mixed extents are shared by up to eight objects.
A new table or index is usually 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. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.
