Data Row Structure in MS SQL Server 6.5

General concepts

Example

Literature

General concepts

There are five kinds of pages in MS SQL 6.5:

Now I want to tell you about how MS SQL 6.5 stores data rows on
the data pages. This is the data row structure:

where Mandatory fields are int, money, datetime, char and
other not null
fields with fixed length.

The Optional fields appear only when there are nullable fields
in the row or fields with variable length (varchar fields).

The Column offset table consists of:

Offset table adjust bytes – the offset of the Pointer to
the Offset table
from the end of the row.

Pointer to the Offset table – the pointer to the Offset
table adjust bytes

Pointers to the place of the Variable fields in the row uses
one byte
for each variable field.

You can use the following command to view the data page structure
(this command is not very good documented in SQL Server Books Online):

DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

PARAMETERS:

   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.

Example

In this example one data page is viewed from the table titleauthor,
database pubs.

USE pubs
GO
DBCC TRACEON (3604)
GO
DECLARE @pgid int
SELECT @pgid = first FROM sysindexes WHERE
  id = object_id('titleauthor') AND indid = 1
DBCC PAGE (pubs, @pgid, 1)
GO

This is the result from my computer:

...
DATA:
Offset 32 -
011e9820:  04042000 3137322d 33322d31 31373650  .. .172-32-1176P
011e9830:  53333333 33016400 0000051a 16150f04  S3333.d.........
...

This is the more simple description:

where byte  0     - Variable fields count = 4
      byte  1     - Row number = 4
      bytes 2-3   - Total row length = 32
      bytes 4-14  - au_id = '172-32-1176'
      bytes 15-20 - title_id = 'PS3333'
      byte  21    - au_ord = 1
      bytes 22-25 - royaltyper = 100
      byte  26    - Offset table adjust bytes = 5
      byte  27    - Pointer to the Offset table = 26
      byte  28    - Pointer to the royaltyper = 22
      byte  39    - Pointer to the au_ord = 21
      byte  30    - Pointer to the title_id = 15
      byte  31    - Pointer to the au_id = 4

Literature

1. SQL Server Books Online.

2. INFO: Description of DBCC PAGE Command

http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP

»


See All Articles by Columnist Alexander Chigrik