Variable-length columns in DOL tables

For a single, variable-length column in a DOL table, the minimum overhead for each row is:

• Six bytes for the initial row overhead.

• Two bytes for the row length.

• Two bytes for the column offset table at the end of the row. Each column offset entry is two bytes. There are n such entries, where n is the number of variable-length columns in the row.

The total overhead is 10 bytes. There is no adjust table for DOL rows. The actual variable-length column size is:

column length + 10 bytes overhead

DOL tables with variable-length columns must have an offset of fewer than 8191 bytes for all inserts to succeed. For example, this insert fails because the offset for columns c2, c3, and c4 is 9010, which exceeds the maximum of 8191 bytes:

create table t1(
	c1 int not null,
	c2 varchar(5000) not null
	c3 varchar(4000) not null
	c4 varchar(10) not null
	... more fixed length columns)
cvarlen varchar(nnn)) lock datarows

The following restrictions apply whether you are using alter table to change a locking scheme or using select into to copy data into a new table.

For servers that use page sizes other than 16K pages, the maximum length of a variable-length column in an APL table is less than that for a DOL table, so you can convert the locking scheme of an APL table with a maximum sized variable-length column to DOL. You cannot, however, convert a DOL table containing at least one maximum sized variable-length column to an APL table.

On servers that use 16K pages, APL tables can store substantially larger sized variable-length columns than DOL tables. You can convert tables from DOL to APL, but you cannot convert from APL to DOL.

These restrictions on locking-scheme conversions occur only if data in the source table exceeds the limits of the target table. If this occurs, Adaptive Server raises an error message while transforming the row format from one locking scheme to the other. If the table is empty, no such data transformation is required, and the lock-change operation succeeds. However, subsequent inserts or updates to the table, users may see errors caused by limitations on the column or row size for the target schema of the altered table.

For DOL tables that use variable-length columns, arrange the columns so that the longest columns are placed toward the end of the table definition. This allows you to create tables with much larger rows than if the large columns appear at the beginning of the table definition. For instance, in a 16K page server, the following table definition is acceptable:

create table t1 (
	c1 int not null,
	c2 varchar(1000) null,
	c3 varchar(4000) null,
	c4 varchar(9000) null) lock datarows

However, the following table definition typically is unacceptable for future inserts. The potential start offset for column c2 is greater than the 8192-byte limit because of the preceding 9000-byte c4 column:

create table t2 (
	c1 int not null,
	c4 varchar(9000) null,
	c3 varchar(4000) null,
	c2 varchar(1000) null) lock datarows

The table is created, but future inserts may fail.