G’day,

I had a recent conversation about data pages. There was some debate about the location of the first record on that page and whether or not it’s location is always after the page header.

As we know the page header is 96 bytes long – so does it follow that the first record is at an offset of 96 – well let’s see.

First we’ll create our test database with a test table and add three records.

USE [tempdb];
GO
IF EXISTS(SELECT 1 FROM [sys].[databases] D WHERE [D].[name] = 'PAGE_TEST')
BEGIN
PRINT 'DROPPING DATABASE [PAGE_TEST]';
ALTER DATABASE [PAGE_TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [PAGE_TEST];
END
GO
CREATE DATABASE PAGE_TEST;
GO
USE [PAGE_TEST]
GO
CREATE TABLE Names
(
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL
);
GO
INSERT Names (FirstName , LastName) VALUES ('Albert' , 'Einstien');
INSERT Names (FirstName , LastName) VALUES ('Isaac' , 'Newton');
INSERT Names (FirstName , LastName) VALUES ('Ernest' , 'Rutherford');
GO

Now lets use DBCC IND to find the details about the page and then plug those details into DBCC page to view the internals of our page.

on my system this is as follows

DBCC TRACEON(3604);
GO
DBCC IND('PAGE_TEST' , 'Names' , 1);
GO
DBCC PAGE (PAGE_TEST , 1,55 ,1)
GO

As can be seen from the image above the first record is at offset 96 (0×60)

Just to doubly confirm this, lets look at the offset array from the same DBCC page output

Now, lets delete the first record that we added to this table.

DELETE Names WHERE FirstName = 'Albert' AND LastName = 'Einstien';
GO

and again run DBCC page

DBCC PAGE (PAGE_TEST , 1,55 ,1)
GO

then check the offset of the first record on the page

And we see that the first record on the page is no longer at offset 96.

Now lets clear all records from the table so that it is empty and then add a few more

DELETE Names;
INSERT Names (FirstName , LastName) VALUES ('Nathan' , 'Cohen');
INSERT Names (FirstName , LastName) VALUES ('Joseph' , 'Sullivan');
INSERT Names (FirstName , LastName) VALUES ('Hamish' , 'Bond');
INSERT Names (FirstName , LastName) VALUES ('Eric' , 'Murray');
INSERT Names (FirstName , LastName) VALUES ('Mahé' , 'Drysdale');
GO

In case you’re wondering about the names, they are New Zealand’s latest gold medalists at the London Olympics.

and let’s look at DBCC page again

DBCC PAGE (PAGE_TEST , 1,55 ,1)
GO

Again the offset of the first record is not 96.

Also, notice that we only have 3 records on the page, despite the fact that we inserted 5 records – what gives?

We’ll, if you look at the output of DBCC IND you’ll see a second data page has been added, also ghosted records would account some of that, me thinks.

But getting back to the original subject of this blog post, we can see that the first record on a data page is not guaranteed to be at offset 96 – immediately after the page header.

Have a good day.

cheers

Martin.