SQL Server Page Types detailed

SQL Server contains several different types of pages to meet the needs of your data store. Regardless of the type of page, their storage structure is the same. Each data file contains a significant number of 8KB pages, each page has 8192bytes available, each page has 96byte for the header storage, and the rest of the space

is used to store the actual data, at the end of the page is the data row offset array, can also be called "page slot" array, we can take a page as a bookcase, which row of data occupies which slot, are marked at the end of the page, and the page Mantissa group write order is flashbacks, so you can effectively use the page space.

It can be foreseen that the "slot" on the page is not necessarily stored in order, when a new ID comes in, and the ID is between the maximum ID and the minimum ID of the page (a leaf page that is sorted by id), then the ID data row is inserted directly behind the existing data row. When a query needs to retrieve the row where the ID is located,

The database engine finds the leaf page from the index page, loads the page into memory, and finds the corresponding row by the row offset array at the end of the page. The record size of the page mantissa group is stored in the page head, and each record of the page slot in the array occupies 2bytes of space.

As far as I know, there are 14 kinds of page types for SQL Server data files:

Type 1--data page:

Data pages in the heap

The leaf page in a clustered index

The location in the data file is random

M_type=1 in DBCC PAGE

Type 2--Index page:

Non-clustered index

Non-"leaf"-Level clustered index

The location in the data file is random

m_type=2 in DBCC PAGE

Type 3--text Mixed page (text Mixed page):

Short-length LOB data types, multiple types, multiple-line storage

The location in the data file is random

M_type=3 in DBCC PAGE

Type 4--text page (Text tree page):

Store a single LOB row

The location in the data file is random

M_type=4 in DBCC PAGE

Type 5--sort page (Sort page):

Temporary page when sorting operations

Common in tempdb, also visible in the "online" operation in user data (for example, when creating an index online without specifying the SORT_IN_TEMPDB option)

The location in the data file is random

M_type=19 in DBCC PAGE