PAGE Type in SQL Server

There are different types of pages in SQL Server. 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. 
• The rest of the space is for data. A variable length row offset array (or slot array) is located at the end of every page
• Count of records (size of array) is saved in the header.
• Records in a page are not sorted even though it is an index page. If the data needs to be sorted, the offset array will be is sorted by the key of the index.

There are about 14 types of pages in SQL Server data file.

• Type 1 – Data page.
• Data records in heap
• Clustered index leaf-level
• Location can be random
• Type 2 – Index page
• Non-clustered index
• Non-leave-level clustered index
• Location can be random
• Type 3 – Text Mixed Page
• Small LOB value(s), multiple types and rows.
• Location can be random
• Type 4 – Text Page
• LOB value from a single column value
• Location can be random
• Type 7 – Sort Page
• Temporary page for sort operation.
• Usually tempdb, but can be in user database for online operations.
• Location can be random
• Type 8 – GAM Page
• Global Allocation Map, track allocation of extents.
• One bit for each extent, if the bit is 1, means the extent is free, otherwise means the extent is allocated (not necessary full).
• The first GAM page in each file is page 2
• Type 9 – SGAM Page
• Shared Global Allocation Map, track allocation of shared extents
• 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
• Type 10 – IAM Page
• Index Allocation Map. Extent allocation in a GAM interval for an index or heap table.
• Location can be random.
• Type 11 – PFS Page
• Page Free Space. Byte map, keeps track of free space of pages
• The first PFS is page 1 in each file.
• Type 13 – Boot Page
• Information about the page
• Only page 9 in file 1.
• Type 14 – Server Configuration Page (It may not be the official name)
• Part of information returned from sp_configure.
• It only exists in master database, file 1, page 10

• Some Added Type in SQL Server 2008
  Type 15 – File Header Page
• Information about the file.
• It’s always page 0 every data page.
• Type 16 – Differential Changed map
• Extents in GAM interval have changed since last full or differential backup
• The first Differential Changed Page is page 6 in each file
• Type 17 – Bulk Change Map
• Extents in GAM interval modified by bulk operations since last backup
• The first Bulk Change Map page is page 7 in each file.

When you know all about your code u start loving it.

Post Reference: Vikram Aristocratic Elfin Share, http://technet.microsoft.com/