FIGURE 7-8 The CD region and short data region in a CD record in Visual Basic .NET

Making UPCA in Visual Basic .NET FIGURE 7-8 The CD region and short data region in a CD record

To locate the entry for a short column value in the short data region, the short data cluster array is rst examined to determine the start address of the containing cluster for the column in the short data region.

7

The Long Data Region Any data in the row longer than 8 bytes is stored in the long data region. This includes complex columns, which do not contain actual data but rather contain information necessary to locate data stored off the row. This can include large object data and row over ow data pointers. Unlike short data, where the length can be stored simply in the CD array, long data needs an actual offset value to allow SQL Server to determine the location of each value. This offset array looks very similar to the offset array I talked about in 5 for the FixedVar records. The long data region is composed of three parts: an offset array, a long data cluster array, and the long data. The offset array is composed of the following:

A 1-byte header. In SQL Server 2008, only the rst two bits are used. Bit 0 indicates if the long data region contains any 2-byte offset values, and in SQL Server 2008, this value is always 1, as all offsets are always 2 bytes. Bit 1 indicates if the long data region contains any complex columns. A 2-byte value indicating the number of offsets to follow. The most signi cant bit in the rst byte of the offset value is used to indicate whether the corresponding entry in the long data region is a complex column or not. The rest of the bits/bytes in the array entry store the ending offset value for the corresponding entry in the long data region.

The long data cluster array is similar to the cluster array for the short data and is used to limit the cost of nding columns near the end of a long list of columns. It has one entry for each 30-column cluster (except the last one). Because we already have the offset of each long data column stored in the offset array, the cluster array just needs to keep track of how many of the long data values are in each cluster. Each value is a one-byte integer representing the number of long data columns in that cluster. Just as for the short data cluster, the number of entries in the cluster array can be computed as (Number of columns in the table 1)/ 30. Figure 7-9 illustrates the long data region for the row described previously, with 64 columns, 9 of which are long. I have not actually included values for the offsets due to space considerations. The long data cluster array has two entries indicating that 6 of the values are in the rst cluster and 2 are in the second. The remaining values are in the last cluster.

Long Data Long data 1 Long data 2 Long data 3 Long data 4 Long data 5 Long data 6 Long data 7 Long data 8 Long data 9

Special Information The end of the row contains three optional pieces of information. The existence of any or all of this information is indicated by bits in the 1-byte header at the very beginning of the row. The three special areas are the following:

Forwarding Pointer This value is used when a heap contains a forwarding stub that points to a new location to which the original row has been moved. Forwarding pointers were discussed in 5. The forwarding pointer contains three header bytes and an 8-byte Row ID. Back Pointer This value is used in a row that has been forwarded to indicate the original location of the row. It is stored as an 8-byte Row ID. Versioning Info When a row is modi ed under Snapshot isolation, SQL Server adds 14 bytes of versioning information to the row. Row versioning and Snapshot isolation is discussed in 10.

Now let s look at the actual bytes in two of the rows in the dbo.Employees_rowcompressed table created previously. The DBCC PAGE command has been enhanced to give additional information about compressed rows and pages. In particular, before the bytes for the row are shown, DBCC PAGE will display the CD array. For the rst row returned on the rst page in the dbo.Employees_rowcompressed table, all the columns contain short data. The row has the following data values:

For short data, the CD array contains the actual length of each of the columns, and we can see the following information for the rst row in the DBCC PAGE output: