Search code examples
sql-serverdbccdata-paging

Missing bytes in SQL Server data page


Did not know how to call the Title properly. However, I am trying to understand how the data pages are stored. I've created simple table:

CREATE TABLE testFix
(
 id INT,
 v CHAR(10)
);

INSERT INTO dbo.testFix
(
    id,
    v
)
VALUES
(   1, -- id - int
    'asdasd' -- v - varchar(100)
) 
GO 2
DBCC TRACEON(3604);

Then I got PageFID and PagePID by following command:

DBCC IND(tempdb, testFix, -1)
GO

Then the actual data pages:

DBCC PAGE (tempdb, 1, 368, 3) 

So now I see:

Slot 0 Offset 0x60 Length 21

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Record Size = 21

Memory Dump @0x000000287DD7A060

0000000000000000: 10001200 01000000 61736461 73642020 20200200 ........asdasd .. 0000000000000014: 00
.

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1

Slot 0 Column 2 Offset 0x8 Length 10 Length (physical) 10

v = asdasd

Slot 1 Offset 0x75 Length 21

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Record Size = 21

Memory Dump @0x000000287DD7A075

0000000000000000: 10001200 01000000 61736461 73642020 20200200 ........asdasd .. 0000000000000014: 00
.

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1

Slot 1 Column 2 Offset 0x8 Length 10 Length (physical) 10

v = asdasd

Slot 2 Offset 0x8a Length 21

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Record Size = 21

Memory Dump @0x000000287DD7A08A

0000000000000000: 10001200 01000000 61736461 73642020 20200200 ........asdasd .. 0000000000000014: 00

So the length of the record is 21 byte. However INT is 4 bytes and CHAR(10) is 10 bytes. 4+10=14. What for the other 7 bytes are used?


Solution

  • Here is the "anatomy" of data row

    enter image description here

    In red there are 7 bytes you are missing: Status Bits A (1), Status Bits B (1), Fdata length (2), Ncols (2), NullBits (1)

    From this book: Pro SQL Server Internals by Korotkevitch D.