Variable length columns in DOL tables

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

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
Table 8-5: Maximum size for variable-length columns in an DOL table

Page size

Maximum row length

Maximum column length

2K (2048 bytes)

1964

1954

4K (4096 bytes)

4012

4002

8K (8192 bytes)

8108

7998

16K (16384 bytes)

16300

162290

DOL tables with variable-length columns must have an offset of less than 8191 bytes for all inserts to succeed. For example, this insert fails because the offset totals more than 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 offset for columns c2, c3, and c4 is 9010, so the entire insert fails.


Restrictions for converting locking schemes or using select into

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. Conversion of a DOL table containing at least one maximum sized variable length column to allpages modeis restricted. Adaptive Server raises an error message and the operation is aborted.On servers that use 16K pages, APL tables can store substantially larger sized variable length columns than can be stored in DOL tables.You can convert tables from DOL to APL, but lock scheme conversion from APL to DOL is restricted. Adaptive Server raises an error message and the operation is aborted. Note that these restrictions on lock scheme conversions occur only if there is data in the source table that goes beyond 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. But, then, on a subsequent insert or update of the table, users might run into errors due to limitations on the column or row-size for the target schema of the altered table.


Organizing columns in DOL tables by size of variable-length columns

For DOL tables that use variable-length columns, arrange the columns so 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 proceeding 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.