The size of the data page in MS SQL 6.5 is 2Kb (2048 bytes). Every data page consists of three parts:
- 32 bytes header
- data rows
- offset table
See Figure 1:
Figure 1. General data page structure
Here are the parts of the page header:
pageno: is a logical page number
nextpg: next logical page number
prevpg: previous logical page number
objid: id of the table
timestamp: internal identifier
nextrno: next row number, that will be written on this page
level: the index level
indid: id of the index
freeoff: pointer to the free space at the end of the page
minlen: minimum rows length for this data page
The offset table contains two bytes for each row on the data page. MS SQL Server 6.5 uses the offset table to find the address of row. Every cell of the offset table contains the address of the row on the data page.
When you insert new record, MS SQL Server 6.5 scans the offset table from the end. If there is cell with a zero value in it, then the offset of the new row will be inserted into this cell. If there is no cell with a zero value in it, then a new cell will be added in the offset table, a new value will be written into freeoff field of the page header, and the nextrno field of the page header will be increased by 1.
When you delete a record, MS SQL Server 6.5 writes in the appropriate cell a zero value, deletes the row, moves other rows to avoid free space between them, and sets the new value into freeoff field of the page header. All rows are stored contiguously on the data page.
You can use the following command to view a data page’s structure (this command is not very well documented in SQL Server 6.5 Books Online):
| DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical]) |
where:
Dbid or dbname: Enter either the dbid or the name of the database in question.
Pagenum: Enter the page number of the SQL Server page that is to be examined.
Print option: (Optional) Print option can be either 0, 1, or 2. 0 – (Default) This option causes DBCC PAGE to print out only the page header information. 1 – This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page’s offset table. Each of the rows printed out will be separated from each other. 2 – This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.
Cache: (Optional) This parameter allows either a 1 or a 0 to be entered. 0 – This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 – (Default) This option takes the page from cache if it is in cache rather than getting it from disk only.
Logical: (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 – If the page is to be a virtual page number. 1 – (Default) If the page is the logical page number.
In the following examples, I am using the trace flag 3604 to better illustrate what is happening in the examples. Trace flag 3604 sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.