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. 0x53translates 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
