Restrictions for alter table

Restrictions that apply when using alter table.

  • Do not alter the system tables.
  • You cannot add a column of datatype bit to an existing table if you specify a default value. This default value must be 0 or 1.

  • When you run alter table drop column against columns with large objects (LOB), the SAP ASE server drops any replication indexes if:
    • The table contains LOB columns, and

    • The table is marked for replication, and

    • The LOB column contains a replication index

    Consequently, the SAP ASE server may require a long time to drop the replication index if the table is large.

  • The maximum number of columns 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 a DOL table

  • alter table raises an error if the number of variable-length columns in an APL table exceeds 254.

  • Drop, then re-create compiled objects after changing a table’s lock schema.

  • You cannot use the no datacopy parameter on:
    • Materialized or virtual computed columns

    • Encrypted columns

    • XML columns

    • timestamp columns

    • bit columns

    • Java columns

  • The maximum length for in-row Java columns is determined by the maximum size of a variable-length column for the table’s schema, locking style, and page size.

  • When converting a table to a different locking scheme, the data in the source table cannot violate the limits of the target table. For example, if you attempt to convert a DOL table with more than 254 variable-length columns to an APL table, alter table fails because an APL table is restricted to having no more than 254 variable-length columns.

  • Columns with fixed-length data (for example char, binary, and so on) have the maximum sizes shown in these two tables. The following describes the maximum row and columns lengths for an APL table:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2KB (2048 bytes)

    1962

    1960 bytes

    4KB (4096 bytes)

    4010

    4008 bytes

    8KB (8192 bytes)

    8106

    8104 bytes

    16KB (16384 bytes)

    16298

    16296 bytes

    The following describes the maximum row and columns lengths for a DOL table:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2KB (2048 bytes)

    1964

    1958 bytes

    4KB (4096 bytes)

    4012

    4006 bytes

    8KB (8192 bytes)

    8108

    8102 bytes

    16KB (16384 bytes)

    16300

    16294 bytes – if table does not include any variable length columns.

    16KB (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 6 bytes for the row overhead and 2 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. The following describes the maximum size columns for an APL table:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2KB (2048 bytes)

    1960

    1960

    4KB (4096 bytes)

    4008

    4008

    8KB (8192 bytes)

    8104

    8157

    16KB (16384 bytes)

    16296

    16227

    The following describes the maximum size columns for a DOL table:

    Page Size

    Maximum Row Length

    Maximum Column Length

    2KB (2048 bytes)

    1960

    1958

    4KB (4096 bytes)

    4008

    4006

    8KB (8192 bytes)

    8157

    8102

    16KB (16384 bytes)

    16294

    16294

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

  • You cannot use the following variable in alter table statements that include defaults:

    declare @a int
    select @a = 2
    alter table t2 add c3 int
    default @a

    Doing so results in error message 154; Variable is not allowed in default.

  • 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().