Welcome back Dear Reader to Part 6 on our series of how to
read a Data Record. In
Part 1 we covered the Tag Bytes. In part
2 we covered the Null Bitmap Offset.
In Part
3 we covered the Fixed Data Portion of a record. And
in Part 4 we talked about the Null Bitmap. In
part 5 we talked about the Variable Offset Array.
Today we will be discussing what
that array was pointing to the Variable Length Data. The nice part is this should be easy going
for you by now because this is very similar to what we did in Part 3. We are reading data.
One of the things that we will use today is what we learned last time to bring it all home. So without fan fair or ado let’s dive right
into it.
VARIABLE LENGTH DATA
First let’s update our chart so we know what part of the
Data Record we are tackling. Once again
these images come by way of Paul Randal (@PaulRandal | Blog) and the MCM Video series
on Data
Structures and the good people from Microsoft.
The variable length columns are the last bit of data that we
need to read out of a regular data record.
The offset array that we looked at yesterday gives us a value for the
end of the variable length record we are looking for so that we can find the
end of our data record and read it in reverse.
We’ll use the same code we’ve been using for the last couple
days but in case you are missing it here it is.
First we’ll create our table and insert a record.
IF EXISTS(SELECT NAME FROM sys.tables WHERE NAME='dataRecord2')
BEGIN
DROP TABLE dataRecord2
END
GO
CREATE TABLE dataRecord2
(myID INT
,myfixedData CHAR(4)
,myVarData1 VARCHAR(6)
,myVarData2 VARCHAR(9)
,myVarData3 VARCHAR(6)
)
GO
INSERT INTO dataRecord2(myID, myfixedData, myVarData1, myVarData2, myVarData3)
VALUES (7, 'XXXX', 'SSS', 'WWWWWWWWW', 'BB')
GO
Now let’s do a DBCC IND and get our page numbers.
DBCC IND(demoInternals, 'dataRecord2', 1)
GO
Remember that a page type 10 in an allocation page and we
are reading a data page. So look for the
page number that has a PageType=1. We’ll
follow that up with a DBCC PAGE on Page 296, remembering to turn on Trace Flag
3604 so that we get our output to our SSMS window. *Remember your page numbers may be different
than mine.
DBCC TRACEON(3604)
GO
DBCC PAGE('demoInternals', 1, 296, 1)
GO
Now that we’ve got our page let’s take a look at it, I’m
only going to post the relevant output.
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 37
Memory Dump @0x0000000014DEA060
0000000000000000:
30000c00 07000000 58585858 05000003 001a0023 0.......XXXX.......#
0000000000000014:
00250053
53535757 57575757 57575742 42 .%.SSSWWWWWWWWWBB
The data that we are looking at is in bold and underlined
and highlighted in red. If we use the
variable offset array values we could find which data belongs to which column. So let’s do that real quick. Don’t forget to use our handy Hex to decimal
and binary conversion tool, http://easycalculation.com/hex-converter.php.
Each block of data in a record is 8 characters long but
stands for 4 bytes of information. So if
we look at all of the blocks we see we have 9 * 4 is 36 bytes. The last bit of information is 42 is two
characters long and is 1 byte long. So our
total record is 37 bytes long. Since
variable length data can have different lengths that changes with the values in
place we need to look at the variable length array to find our data and read it
forward.
Our first Variable length column is 53
5353. Because this is regular character data we will translate one byte
at a time. 0x53
translates to binary 01010011, if we use our
binary to ascii conversion tool, , then we find that binary value translates to
S. There
are three 53 values and our first variable length column has ‘SSS’ as it’s the
value that we inserted into myVarData1.
Following this pattern you could very easily translate the contents of
the other two variable length columns.
I’ll leave you to it Dear Reader, as always thanks for
stopping by.
Thanks,
Brad
