Special Indexing and Storage Features - Pro SQL Server Internals - page 100

The Long Data Region starts with an offset array, which is similar to a variable-length offset array in the FixedVar
row format.
The first byte is a bitmask with two meaningful bits. The first one is always 1, which tells SQL Server that the offset
array uses 2-byte values. The second bit indicates if the row has any complex columns that store data off-row.
The next two bytes store the number of columns in an offset array to follow.
The most significant bit in the first byte of each element in the offset array indicates if it is a complex column.
Other bits store the ending offset of the column.
Similar to the Short Data Region, SQL Server optimizes access to the trailing columns by storing an array with
a number of long data columns in each 30-column cluster. Figure 4-17 illustrates the Long Data Region for the same
row shown in Figure 4-16 .
Figure 4-17. Example of Long Data region data
Let's examine the actual row data and create a table, as shown in Listing 4-26.
Listing 4-26. Row Compression: Creating a table
create table dbo.RowCompressionData
(
Int1 int,
Int2 int,
Int3 int,
VarChar1 varchar(1000),
VarChar2 varchar(1000),
Bit1 bit,
Bit2 bit,
Char1 char(1000),
Char2 char(1000),
Char3 char(1000)
)
with (data_compression=row);
insert into dbo.RowCompressionData
values
(0 /*Int1*/, 2147483647 /*Int2*/, null /*Int3*/
,'aaa'/*VarChar1*/,replicate('b',1000) /*VarChar2*/
,0 /*BitCol1*/, 1 /*BitCol2*/, null /*Char1*/
, replicate('c',1000) /*Char2*/
,'dddddddddd' /*Char3*/);