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