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:

  • Data pages

  • Index pages

  • Allocation pages

  • Text/Image pages

  • Distribution pages
  • I have told you about data page structure in my previous article
    http://www.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])

    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:

















    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

    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

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.
    Previous articleAutomagic Auditing
    Next articleANSI Joins

    Latest Articles