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 upto 8KB of data. Below are the various types of pages available in SQL Servers:

Data Pages

Data rows with all data, except text, ntext, 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 contain 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.

Image / Text Pages

These pages are used to store the data that are larger than the normal data, (Example: Storing Resume in the database), stores up to 2 GB.

Large object data types:

text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data

Variable length columns when the data row exceeds 8 KB:

varchar, nvarchar, varbinary, and sql_variant

Index Allocation Map (IAM)

 

Stores the address of the objects. Above is an index allocation map, this contains the address of all the tables.(left side is the table name and right side it is the address where the table is stored). Information about extents used by a table or index per allocation unit.

Global Allocation Map (GAM)

It stores the information whether the extents are allocated or not.

Secondary Global Allocation Map (SGAM)

It also stores the information whether the extents are allocated or not.

Page Free Space(PFS)

This page contains the information of available free space in data pages and about the allocation of page.

Bulk Changed Map (BCM)

This page contains the information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.

Differential Changed Map (DCM)

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

Leave a comment

1 Comment

  1. Ambica

     /  May 5, 2011

    Its very useful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: