All SQL Server physical structure is based on 2K blocks called pages. The page is the fundamental building block for SQL Server objects, such as tables and indexes. It is also the fundamental unit for most SQL Server actions, such as reading, writing, caching, and locking.
There are six types of pages in a SQL Server database, as shown in the following table.
Page type | Contains |
Data | Data rows |
Index | Index rows |
Log | Log rows recording data changes for use in recovery |
Text/Image | Text and image data |
Distribution | Distribution statistics on indexes for the purpose of optimizing queries |
Allocation | Data allocation information used by SQL Server to manage space in a database |
Pages for many objects, such as data pages, log pages, and text/image pages, are chained together in linked lists. These are called page chains, such as the data-page chain for a table, or a chain of text pages for a row.
Every page has a header containing standard information such as the object ID of the object the page is allocated to and the next/previous pointers that link page chains.
Every page in a database is assigned a page number, called a logical page number. The first page in a database is logical page 0, and the page numbers increment by 1 to the last page in the database. Since the logical page numbers in each database start at 0, logical page numbers are duplicated among databases. It also means a logical page number is relevant only if there is also an indication of which database the page is in. The page pointers that link together page chains are simply the logical page number of the page that the pointer references.