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