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.