The maximum number of columns you can create in a table is:
1024 for fixed-length columns in both all-pages-locked (APL) and data-only- locked (DOL) tables
254 for variable-length columns in an APL table
1024 for variable-length columns in an DOL table
The maximum size of a column depends on:
Whether the table includes any variable- or fixed-length columns.
The logical page size of the database. For example, in a database with 2K logical pages, the maximum size of a column in an APL table can be as large as a single row, about 1962 bytes, less the row format overheads. Similarly, for a 4K page, the maximum size of a column in a APL table can be as large as 4010 bytes, less the row format overheads. See Table 0-1 for more information.
If you attempt to create a table with a fixed-length column that is greater than the limits of the logical page size, create table issues an error message.
Locking scheme |
Page size |
Maximum row length |
Maximum column length |
|---|---|---|---|
2K (2048 bytes) |
1962 |
1960 bytes |
|
4K (4096 bytes) |
4010 |
4008 bytes |
|
APL tables |
8K (8192 bytes) |
8106 |
8104 bytes |
16K (16384 bytes) |
16298 |
16296 bytes |
|
2K (2048 bytes) |
1964 |
1958 bytes |
|
4K (4096 bytes) |
4012 |
4006 bytes |
|
DOL tables |
8K (8192 bytes) |
8108 |
8102 bytes |
16K (16384 bytes) |
16300 |
16294 bytes if table does not include any variable length columns |
|
16K (16384 bytes) |
16300 (subject to a max start offset of varlen = 8191) |
8191-6-2 = 8183 bytes if table includes at least on variable length column.* |
|
* This size includes six bytes for the row overhead and two bytes for the row length field |
|||
The maximum size of a fixed-length column in a DOL table with a 16K logical page size depends on whether the table contains variable-length columns. The maximum possible starting offset of a variable-length column is 8191. If the table has any variable-length columns, the sum of the fixed-length portion of the row, plus overheads, cannot exceed 8191 bytes, and the maximum possible size of all the fixed-length columns is restricted to 8183 bytes, when the table contains any variable-length columns.
