Data Row Structure in MS SQL Server 6.5

General concepts

There are five kinds of pages in MS SQL 6.5:

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:

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