-- Through this small demo we will see how data is stored in a page and how to
decode it. Create a database ExplorePage as shown below
CREATE DATABASE ExplorePage
GO
USE ExplorePage
GO
-- Create a table that has atleast one fixed length and one variable length column
CREATE TABLE Page
(
IntType Int NOT NULL PRIMARY KEY
,CharType Char(2)
,VarcharType Varchar(5)
)
GO
-- Insert 2 rows in this table as shown below
INSERT INTO Page VALUES (1, 'AB', 'TEXT');
INSERT INTO Page VALUES (2, NULL, 'MORE');
GO
-- As shown below 2 rows have been inserted into the table
-- Let us see the pages that has been allocated to this table. As can be seen in the snapshot below page 150 is an IAM page (PageType = 10) and page 147 is -- data page (PageType = 1)
DBCC IND (ExplorePage, Page, 1)
-- Using DBCC PAGE command let's read this page as show below. For this example we will not focus on the header as it is already explained in my previous -- blogs. Let us focus on the data section
DBCC TRACEON (3604)
DBCC PAGE (ExplorePage, 1, 147, 3)
Slot 0 Offset 0x60 Length 21
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x000000000E20A060
0000000000000000: 30000a00 01000000 41420300 00010015 †0.......AB......
0000000000000010: 00544558 54††††††††††††††††††††††††††.TEXT
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
IntType = 1
Slot 0 Column 2 Offset 0x8 Length 2 Length (physical) 2
CharType = AB
Slot 0 Column 3 Offset 0x11 Length 4 Length (physical) 4
VarcharType = TEXT
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (8194443284a0)
Slot 1 Offset 0x75 Length 21
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x000000000E20A075
0000000000000000: 30000a00 02000000 00000300 02010015 †0...............
0000000000000010: 004d4f52 45††††††††††††††††††††††††††.MORE
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
IntType = 2
Slot 1 Column 2 Offset 0x0 Length 0 Length (physical) 0
CharType = [NULL]
Slot 1 Column 3 Offset 0x11 Length 4 Length (physical) 4
VarcharType = MORE
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (61a06abd401c)
-- From this extract let's read the data (hex dump) for the first record, which is something as shown below. All the explaination below uses this dump unless -- specified otherwise
Record 1 hex dump :___________________
___________________
30000a00 01000000 41420300 00010015 †0.......AB......
00544558 54††††††††††††††††††††††††††.TEXT
Byte 0 expands as shown below
Byte 0 = 30 = 0011 0000 (binary)
-- First 3 bits here can have the follwing possible value
000 - Primary record
001 - Forwared record (when the data becomes large enough to fit into its original page
010 - Forwarding record
011 - Index record
100 - Blob fragment
101 - Ghost index record (deleted row)
110 - Ghost data record (deleted row)
111 - Ghost version record
In our example the first 3 bits are 000, thus it is a PRIMARY RECORD
Byte 1 = can either be 0x00 or 0x01. If it is 0x01, that means the record type is ghost forwarded record
Byte 2 & 3 = 0a 00 = a0 00 (Reversing bytes to read) = 000a (Complete Reverse (hex)) = 10 (decimal). It says that the NULL BITMAP is at offset 10 from the byte 2. In this case the value is 00 (first 2 bits in 00010015). The value 00 signifies none of the column has a NULL value as expanded below
00 = 0000 0000 = Table has 3 columns and since first 3 bits are 0 which means all columns have value.
Byte 3 to 7 = 01 00 00 00 = 10 00 00 00 (Reversing bytes to read) = 00 00 00 01 (Complete Reverse (hex)) = 1 (decimal). This is the value of the first column for the first row as inserted by us. Since int takes 4 bytes, byte 3-7 are used by it.
Byte 8 & 9 = 41(HEX) 42(HEX) = 65(DEC) 66(DEC) = A (CHAR(65)) B (CHAR(66)) = AB. As can be seen this is the second column AB. Since char data is fixed width is lies along side int and takes 2 bytes (1 byte per character).
Byte 10 & 11 = 03 00 = 30 00 (Reversing bytes to read) = 00 03 (Complete Reverse (hex)) = 3 (decimal). This shows the number of columns in this record. Since there are 3 columns, so is the value 3.
Byte 12 = 00 (we have already discussed as the NULL BITMAP (offset 10 from Byte 2)
Byte 13 & 14 = 01 00 = 10 00 (Reversing bytes to read) = 00 01 (Complete Reverse (hex)) = 1 (decimal). This is the count of variable length columns in the record. In this case there is only one as VarcharType
Byte 15 & 16 = 15 00 = 51 00 (Reversing bytes to read) = 00 15 (Complete Reverse (hex)) = 21 (decimal). It is the variable length column offset array. This says that the variable length column ends at offset 21. Notice that the start offset of the variable length column value isn't stored - it doesn't need to be
because by definition it must begin right after the last offset in the variable length column offset array.In this case the variable length column offset array ebds at Byte 16, so Byte 17 to 20 is 54455854 as expanded below
54(HEX) 45(HEX) 58(HEX) 54(HEX) = 84(DEC) 69(DEC) 88(DEC) 84(DEC) = T (CHAR(84)) E (CHAR(69)) X (CHAR(88)) T (CHAR(84)) = TEXT
Record 2 hex dump :___________________
___________________
0000000000000000: 30000a00 02000000 00000300 02010015 †0...............
0000000000000010: 004d4f52 45††††††††††††††††††††††††††.MORE
Byte 0 expands as shown below
Byte 0 = 30 = 0011 0000 (binary)
-- First 3 bits here can have the follwing possible value
000 - Primary record
001 - Forwared record (when the data becomes large enough to fit into its original page
010 - Forwarding record
011 - Index record
100 - Blob fragment
101 - Ghost index record (deleted row)
110 - Ghost data record (deleted row)
111 - Ghost version record
In our example the first 3 bits are 000, thus it is a PRIMARY RECORD
Byte 1 = can either be 0x00 or 0x01. If it is 0x01, that means the record type is ghost forwarded record
Byte 2 & 3 = 0a 00 = a0 00 (Reversing bytes to read) = 000a (Complete Reverse (hex)) = 10 (decimal). It says that the NULL BITMAP is at offset 10 from the byte 2. In this case the value is 02 (first 2 bits in 02010015). The value 02 signifies second column has a NULL value as expanded below
02 = 0000 0010 = since second bit from left is 1 which means column has a null value (indeed true!!).
Byte 3 to 7 = 02 00 00 00 = 20 00 00 00 (Reversing bytes to read) = 00 00 00 02 (Complete Reverse (hex)) = 2 (decimal). This is the value of the first column for the second row as inserted by us. Since int takes 4 bytes, byte 3-7 are used by it.
Byte 8 & 9 = 00(HEX) 00(HEX) = 00(DEC) 00(DEC) = Blank (CHAR(00)) Blank (CHAR(00)) = Blank. As can be seen this is the second column AB. Since char data is fixed width is lies along side int and takes 2 bytes (1 byte per character).
Byte 10 & 11 = 03 00 = 30 00 (Reversing bytes to read) = 00 03 (Complete Reverse (hex)) = 3 (decimal). This shows the number of columns in this record. Since there are 3 columns, so is the value 3.
Byte 12 = 02 (we have already discussed as the NULL BITMAP (offset 10 from Byte 2).
Byte 13 & 14 = 01 00 = 10 00 (Reversing bytes to read) = 00 01 (Complete Reverse (hex)) = 1 (decimal). This is the count of variable length columns in the record. In this case there is only one as VarcharType
Byte 15 & 16 = 15 00 = 51 00 (Reversing bytes to read) = 00 15 (Complete Reverse (hex)) = 21 (decimal). It is the variable length column offset array. This says that the variable length column ends at offset 21. Notice that the start offset of the variable length column value isn't stored - it doesn't need to be
because by definition it must begin right after the last offset in the variable length column offset array. In this case the variable length column offset array ebds at Byte 16, so Byte 17 to 20 is 4d4f5245 as expanded below
4d(HEX) 4f(HEX) 52(HEX) 45(HEX) = 77(DEC) 79(DEC) 82(DEC) 69(DEC) = M (CHAR(84)) O (CHAR(69)) R (CHAR(88)) E (CHAR(84)) = MORE
It's awesome to be ables to decode it!!!
CREATE DATABASE ExplorePage
GO
USE ExplorePage
GO
-- Create a table that has atleast one fixed length and one variable length column
CREATE TABLE Page
(
IntType Int NOT NULL PRIMARY KEY
,CharType Char(2)
,VarcharType Varchar(5)
)
GO
-- Insert 2 rows in this table as shown below
INSERT INTO Page VALUES (1, 'AB', 'TEXT');
INSERT INTO Page VALUES (2, NULL, 'MORE');
GO
-- As shown below 2 rows have been inserted into the table
-- Let us see the pages that has been allocated to this table. As can be seen in the snapshot below page 150 is an IAM page (PageType = 10) and page 147 is -- data page (PageType = 1)
DBCC IND (ExplorePage, Page, 1)
-- Using DBCC PAGE command let's read this page as show below. For this example we will not focus on the header as it is already explained in my previous -- blogs. Let us focus on the data section
DBCC TRACEON (3604)
DBCC PAGE (ExplorePage, 1, 147, 3)
Slot 0 Offset 0x60 Length 21
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x000000000E20A060
0000000000000000: 30000a00 01000000 41420300 00010015 †0.......AB......
0000000000000010: 00544558 54††††††††††††††††††††††††††.TEXT
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
IntType = 1
Slot 0 Column 2 Offset 0x8 Length 2 Length (physical) 2
CharType = AB
Slot 0 Column 3 Offset 0x11 Length 4 Length (physical) 4
VarcharType = TEXT
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (8194443284a0)
Slot 1 Offset 0x75 Length 21
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x000000000E20A075
0000000000000000: 30000a00 02000000 00000300 02010015 †0...............
0000000000000010: 004d4f52 45††††††††††††††††††††††††††.MORE
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
IntType = 2
Slot 1 Column 2 Offset 0x0 Length 0 Length (physical) 0
CharType = [NULL]
Slot 1 Column 3 Offset 0x11 Length 4 Length (physical) 4
VarcharType = MORE
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (61a06abd401c)
-- From this extract let's read the data (hex dump) for the first record, which is something as shown below. All the explaination below uses this dump unless -- specified otherwise
Record 1 hex dump :___________________
___________________
30000a00 01000000 41420300 00010015 †0.......AB......
00544558 54††††††††††††††††††††††††††.TEXT
Byte 0 expands as shown below
Byte 0 = 30 = 0011 0000 (binary)
-- First 3 bits here can have the follwing possible value
000 - Primary record
001 - Forwared record (when the data becomes large enough to fit into its original page
010 - Forwarding record
011 - Index record
100 - Blob fragment
101 - Ghost index record (deleted row)
110 - Ghost data record (deleted row)
111 - Ghost version record
In our example the first 3 bits are 000, thus it is a PRIMARY RECORD
Byte 1 = can either be 0x00 or 0x01. If it is 0x01, that means the record type is ghost forwarded record
Byte 2 & 3 = 0a 00 = a0 00 (Reversing bytes to read) = 000a (Complete Reverse (hex)) = 10 (decimal). It says that the NULL BITMAP is at offset 10 from the byte 2. In this case the value is 00 (first 2 bits in 00010015). The value 00 signifies none of the column has a NULL value as expanded below
00 = 0000 0000 = Table has 3 columns and since first 3 bits are 0 which means all columns have value.
Byte 3 to 7 = 01 00 00 00 = 10 00 00 00 (Reversing bytes to read) = 00 00 00 01 (Complete Reverse (hex)) = 1 (decimal). This is the value of the first column for the first row as inserted by us. Since int takes 4 bytes, byte 3-7 are used by it.
Byte 8 & 9 = 41(HEX) 42(HEX) = 65(DEC) 66(DEC) = A (CHAR(65)) B (CHAR(66)) = AB. As can be seen this is the second column AB. Since char data is fixed width is lies along side int and takes 2 bytes (1 byte per character).
Byte 10 & 11 = 03 00 = 30 00 (Reversing bytes to read) = 00 03 (Complete Reverse (hex)) = 3 (decimal). This shows the number of columns in this record. Since there are 3 columns, so is the value 3.
Byte 12 = 00 (we have already discussed as the NULL BITMAP (offset 10 from Byte 2)
Byte 13 & 14 = 01 00 = 10 00 (Reversing bytes to read) = 00 01 (Complete Reverse (hex)) = 1 (decimal). This is the count of variable length columns in the record. In this case there is only one as VarcharType
Byte 15 & 16 = 15 00 = 51 00 (Reversing bytes to read) = 00 15 (Complete Reverse (hex)) = 21 (decimal). It is the variable length column offset array. This says that the variable length column ends at offset 21. Notice that the start offset of the variable length column value isn't stored - it doesn't need to be
because by definition it must begin right after the last offset in the variable length column offset array.In this case the variable length column offset array ebds at Byte 16, so Byte 17 to 20 is 54455854 as expanded below
54(HEX) 45(HEX) 58(HEX) 54(HEX) = 84(DEC) 69(DEC) 88(DEC) 84(DEC) = T (CHAR(84)) E (CHAR(69)) X (CHAR(88)) T (CHAR(84)) = TEXT
Record 2 hex dump :___________________
___________________
0000000000000000: 30000a00 02000000 00000300 02010015 †0...............
0000000000000010: 004d4f52 45††††††††††††††††††††††††††.MORE
Byte 0 expands as shown below
Byte 0 = 30 = 0011 0000 (binary)
-- First 3 bits here can have the follwing possible value
000 - Primary record
001 - Forwared record (when the data becomes large enough to fit into its original page
010 - Forwarding record
011 - Index record
100 - Blob fragment
101 - Ghost index record (deleted row)
110 - Ghost data record (deleted row)
111 - Ghost version record
In our example the first 3 bits are 000, thus it is a PRIMARY RECORD
Byte 1 = can either be 0x00 or 0x01. If it is 0x01, that means the record type is ghost forwarded record
Byte 2 & 3 = 0a 00 = a0 00 (Reversing bytes to read) = 000a (Complete Reverse (hex)) = 10 (decimal). It says that the NULL BITMAP is at offset 10 from the byte 2. In this case the value is 02 (first 2 bits in 02010015). The value 02 signifies second column has a NULL value as expanded below
02 = 0000 0010 = since second bit from left is 1 which means column has a null value (indeed true!!).
Byte 3 to 7 = 02 00 00 00 = 20 00 00 00 (Reversing bytes to read) = 00 00 00 02 (Complete Reverse (hex)) = 2 (decimal). This is the value of the first column for the second row as inserted by us. Since int takes 4 bytes, byte 3-7 are used by it.
Byte 8 & 9 = 00(HEX) 00(HEX) = 00(DEC) 00(DEC) = Blank (CHAR(00)) Blank (CHAR(00)) = Blank. As can be seen this is the second column AB. Since char data is fixed width is lies along side int and takes 2 bytes (1 byte per character).
Byte 10 & 11 = 03 00 = 30 00 (Reversing bytes to read) = 00 03 (Complete Reverse (hex)) = 3 (decimal). This shows the number of columns in this record. Since there are 3 columns, so is the value 3.
Byte 12 = 02 (we have already discussed as the NULL BITMAP (offset 10 from Byte 2).
Byte 13 & 14 = 01 00 = 10 00 (Reversing bytes to read) = 00 01 (Complete Reverse (hex)) = 1 (decimal). This is the count of variable length columns in the record. In this case there is only one as VarcharType
Byte 15 & 16 = 15 00 = 51 00 (Reversing bytes to read) = 00 15 (Complete Reverse (hex)) = 21 (decimal). It is the variable length column offset array. This says that the variable length column ends at offset 21. Notice that the start offset of the variable length column value isn't stored - it doesn't need to be
because by definition it must begin right after the last offset in the variable length column offset array. In this case the variable length column offset array ebds at Byte 16, so Byte 17 to 20 is 4d4f5245 as expanded below
4d(HEX) 4f(HEX) 52(HEX) 45(HEX) = 77(DEC) 79(DEC) 82(DEC) 69(DEC) = M (CHAR(84)) O (CHAR(69)) R (CHAR(88)) E (CHAR(84)) = MORE
It's awesome to be ables to decode it!!!
