Restrictions for create table

Restrictions for using create table.

  • The maximum number of columns in a table depends on the width of the columns and the server’s logical page size:

    • The sum of the columns’ sizes cannot exceed the server’s logical page size.

    • The maximum number of columns per table cannot exceed 1024.

    • The maximum number of variable-length columns for an all-pages lock table is 254.

      For example, if your server uses a 2K logical page size and includes a table of integer columns, the maximum number of columns in the table is far fewer than 1024. (1024 * 4 bytes exceeds a 2K logical page size.)

      You can mix variable- and fixed-length columns in a single table as long as the maximum number of columns does not exceed 1024. For example, if your server uses a 8K logical page size, a table configured for APL can have 254 nullable integer columns (these are variable-length columns) and 770 non-nullable integers, for a total of 1024 columns.

  • There can be as many as 2,000,000,000 tables per database and 1024 user-defined columns per table. The number of rows per table is limited only by available storage.

  • Although the SAP ASE server does create tables in the following circumstances, you receive errors about size limitations when you perform DML operations:

    • If the total row size for rows with variable-length columns exceeds the maximum column size

    • If the length of a single variable-length column exceeds the maximum column size

    • For data-only-locked tables, if the offset of any variable-length column other than the initial column exceeds the limit of 8191 bytes

  • The SAP ASE server reports an error if the total size of all fixed-length columns, plus the row overhead, is greater than the table’s locking scheme and page size allows. These limits are for APL tables are:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2K (2048 bytes)

    1962 bytes

    1960 bytes

    4K (4096 bytes)

    4010 bytes

    4008 bytes

    8K (8192 bytes)

    8106 bytes

    8104 bytes

    16K (16384 bytes)

    16298 bytes

    16296 bytes
    The limits for DOL tables are:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2K (2048 bytes)

    1964 bytes

    1958 bytes

    4K (4096 bytes)

    4012 bytes

    4006 bytes

    8K (8192 bytes)

    8108 bytes

    8102 bytes

    16K (16384 bytes)

    16300 bytes

    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 one variable-length column.

    This size includes six bytes for the row overhead and two bytes for the row length field

  • The maximum number of bytes of variable length data per row depends on the locking scheme for the table:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2K (2048 bytes)

    1962

    1960

    4K (4096 bytes)

    4010

    4008

    8K (8192 bytes)

    8096

    8104

    16K (16384 bytes)

    16298

    16296

    The maximum size of columns for a DOL table:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2K (2048 bytes)

    1964

    1958

    4K (4096 bytes)

    4012

    4006

    8K (8192 bytes)

    8108

    8102

    16K (16384 bytes)

    16300

    16294

  • If you create a DOL table with a variable-length column that exceeds a 8191-byte offset, you cannot add any rows to the column.

  • If you create tables with varchar, nvarchar, univarchar, or varbinary columns for which total defined width is greater than the maximum allowed row size, a warning message appears, but the table is created. If you try to insert more than the maximum number bytes into such a row, or to update a row so that its total row size is greater than the maximum length, the SAP ASE server produces an error message, and the command fails.

  • When a create table command occurs within an if...else block or a while loop, the SAP ASE server creates the schema for the table before determining whether the condition is true. This may lead to errors if the table already exists. To avoid this situation, either make sure a view with the same name does not already exist in the database or use an execute statement, as follows:

    if not exists
    (select * from sysobjects where name="my table")
begin
execute "create table mytable (x int)"
end

  • You cannot issue create table with a declarative default or check constraint and then insert data into the table in the same batch or procedure. Either separate the create and insert statements into two different batches or procedures, or use execute to perform the actions separately.

  • You cannot use the following variable in create table statements that include defaults:
    declare @p int
select @p = 2
create table t1 (c1 int default @p, c2 int)

    Doing so results in error message 154: “Variable is not allowed in default.”

  • Virtually-hashed tables have these restrictions:

    SQL user-defined functions are not currently supported with create proxy table, create table at remote server, or alter table.

    Note: The execution of SQL functions requires the syntax username.functionname().

    • Virtually-hashed tables must have unique rows. Virtually hashed tables do not allow multiple rows with the same key-column values because the SAP ASE server cannot keep one row in the hash region and another with the same key-column value in the overflow clustered region.

    • You must create each virtually-hashed table on an exclusive segment.

Parent topic: create table