The Page in a SQL Server is a small storage unit of data and it resides in a Data File. When we write first row in a table, It creates one 8KB page to store that data and it assigns 0 as a starting number and sequentially increase in a data file.
Each data file has a unique file ID number and each page has a unique page number.
We can store 128 pages per one megabyte.
The 8KB page doesn’t associate with single row of a data. If we have smaller row, multiple row can store into one 8KB data page and If we have bigger row and 8KB page is not sufficient to store it, It creates multiple pages.
The data page is a separate for each table and it never merges data from multiple tables. Even if we have small size of the table, it also creates separate pages for each table data.
SQL Server uses different types of pages to store different types of data like: Index, Data, Text, BLOB.
The different type of pages are:
Data: stores user’s data, but type of text, ntext, nvarchar(max),varchar(max),varbinary(max),xml, and image.
Index: stores index related data.
Text/Image: stores all large object data type like: text,varchar(max).
GAM: stores global allocation map information about extents.
Page Free Space: stores information about the free space of the pages.
IAM: stores index allocation map information about the indexes.
BCM: stores bulk changed map information of the extents.
The internal structure of the Page:
A Data Page consists of a three main sections which are Page Header, Actual Data and Row offsets.
Each Data Page has 96-byte header to store system information like: page number, page type, free space, page owner’s object id.
After the header information, actual data rows are put on the page serially.
There is one row offset table at the end of the page and it stores information about the each row.
The records within a page are not always stored in continuous logical order and the free-space on a page is not always maintained in one contiguous block.
If we delete a record, it creates one free hole within the page, but immediately it cannot be used by new INSERT.
It creates fragmentation in a page and when it is necessary, INSERT check the free block and try to insert best match record into that free block.
This all row’s information kept by the Row offset table.
The Database Administrator has to also perform some maintenance task to remove fragmentation from the data page.