Types of Pages in SQL Server

Pages are the basic storage unit of SQL Server. There are several pages available in SQL Server. Each page can store up to 8KB of data. No matter what types of pages are, the layout of the page is the same. A data file consists of numbers of 8k-pages. A Page includes 8192 bytes. First 96 bytes are used for header and 2 byte use for Row offset. The rest of the space is for data. 

There are 14 types of pages in SQL Server

Page Type	Page name
1	Data page
2	Index page
3	Text Mix page
4	Text page
7	Sort page
8	GAM (Global allocation map) page
9	SGAM(shared GAM) page
10	IAM page
11	PFS Page
13	Boot page
14	Server configuration page
15	File header page
16	Differential changed map
17	Bulk change map

Page Type 1 – (Data page)

Data rows with all data, except text, next, image, nvarchar (max), varchar (max), varbinary (max), and xml data. The actual data in your table is stored in Pages, except BLOB data. If a column contains BLOB data then a 16 byte pointer is used to reference the BLOB page. The Page is the smallest unit of data storage in Microsoft SQL Server. A page contains the data in the rows. A row can only reside in one page. Each Page can contain 8KB of information, due to this; the maximum size of a Row is 8KB. A group of 8 adjacent pages is called an extent. A heap is a collection of data pages.

Page Type – 2 (Index page)

This page is use for indexing purpose. Also contain the non-cluster index leaf information.

Page Type-3 (Text Mix page)

Use for small LOB values for multiple rows and for multiple type.

Page Type-4 (Text Page)

Used for LOB value for single column.

Page Type -7 (Sort Page)

Used for sort operation on temporary page. It is mostly use in tempdb database.

Page Type-8 (GAM page)

Global Allocation Map page, It is use to track the allocation of Extends. One bit for each extent, if the bit is 1, means the extent is free, otherwise means the extent is allocated (not necessary full).T he first GAM page in each file is page 2

Page Type-9 (SGAM)

Shared Global Allocation Map, It is use to track the allocation of the shared Extends. One bit for each extent, if the bit is 1, means the extent is allocated but has free space; otherwise means the extent is full. The first SGAM page in each file is page 3

Page Type-10 (IAM Page)

Index Allocation Map, It contains the page information, Memory information. Stores the address of the objects. This contains the address of all the tables.

Page Type -11 (PFS Page)

Page free space, it contains the information of free space of pages.

 Page Type-13 (Boot Page)

Contain the information about the page.

Page Type-14 (server configuration page)

Contain the information which is return from SP_Configure.

Page Type-15 (File Header page)

Contain the information about the file.

Page Type-16 (Differential Changed Map page)

This page contains the information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

Page Type-17 (Bulk Change Map page)

It contains extends information in GAM interval that have modified by bulk operations since last backup.