TYPES OF PAGES IN SQL SERVER 2005

1. Data Pages

2. Index Pages

3. IAM ( Index Allocation Map )

4. GAM ( Global Allocation Map )

5. SGAM ( Secondary Global Allocation Map )

DATA PAGES : It holds the data of the table for databases.The format of the page is header , data rows and row id. The row id starts from right to left. Data row contains the actual data of rows.Header size that is 96 bytes.

INDEX PAGES : They hold the information about indexes.SIZE of 8KB each. Indexes are actually B tree like structures.Size of header is not fixed.

NOTE : Extents are independent of the type of pages.

INDEX ALLOCATION MAP ( IAM ) : It is a bit map page. This page tells where the data pages of that particular object lies; Means it gives us path for data pages of a particular object.Each object will have a minimum IAM page. 1 IAM Page manages 64000 extents.One object can have more than 1 IAM page.The address of the IAM page can be collected from sysindex table.

Please note that SysIndex is a system table.IAM gives the address of the 1st page of a particular object.

GLOBAL Allocation MAP ( GAM ) : It is a bit map[0 or 1] .It tells us which extent is already allocated and which is not allocated. In case the extent is allocated the value is 0 and if the extent is not allocated the value would be 1. 1 GAM can manage 8000 extents only.1 GAM is of 8 KB size only.For each extent there is only one entry in the GAM.

SECONDARY  GLOBAL ALLOCATION MAP ( SGAM ) . It is a bit map page.It can manage 8000 extents.It tells us which extent is mixed and which extent is uniform.The value for mixed extent is 1 and for uniform the value is 0.

Pawan Kumar

Pawankkmr@hotmail.com