Data Row Structure in MS SQL Server 6.5

Sunday May 21st 2000 by Alexander Chigrik

Alexander Chigrik takes a look at how MS SQL 6.5 stores data rows on data pages in this article.

General concepts

General concepts

There are five kinds of pages in MS SQL 6.5:

  • Data pages
  • Index pages
  • Allocation pages
  • Text/Image pages
  • Distribution pages
  • I have told you about data page structure in my previous article http://mobile.databasejournal.com/features/mssql/article.php/1443621

    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:

    Mandatory Optional
    Variable fields count (1 byte) Row number(1 byte) Fixed fields data Total row length (2 byte) Variable fields data Column offset table

    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
  • Pointer to the Offset table
  • Pointers to the place of the Variable fields in the row
  • 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])


       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
               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 this example one data page is viewed from the table titleauthor, database pubs.

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

    This is the result from my computer:

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

    This is the more simple description:

    0 1 2 4 15 21 22 26 27 28 29 30 31

    4 4 32 172-32-1176 PS3333 1 100 5 26 22 21 15 4

    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


    1. SQL Server Books Online.

    2. INFO: Description of DBCC PAGE Command

    » See All Articles by Columnist Alexander Chigrik