alter table

Description

Adds, changes or drops columns; adds, changes, or drops constraints; partitions or unpartitions an existing table; changes the locking scheme for an existing table; specifies ascending or descending index order when alter table is used to create referential integrity constraints that are based on indexes; specifies the ratio of filled pages to empty pages, to reduce storage fragmentation.

Syntax

alter table [database.[owner].]table_name 
 {add column_name datatype
    [default {constant_expression | user | null}]
 {identity | null | not null}
     | [[constraint constraint_name]
       {{unique | primary key}
   [clustered | nonclustered] [asc | desc]
   [with { { fillfactor = pct
                   | max_rows_per_page = num_rows }
                   , reservepagegap = num_pages }]
          [on segment_name]
        | references [[database.]owner.]ref_table 
           [(ref_column)]
        | check (search_condition) ] ... }
     [, next_column]...

| add { [constraint constraint_name]
      { {unique | primary key}
           [clustered | nonclustered]
           (column_name [asc | desc]
                [, column_name [asc | desc]...])
        [with { { fillfactor = pct
                | max_rows_per_page = num_rows}
                , reservepagegap = num_pages}]
        [on segment_name]
   | foreign key (column_name [{, column_name}...])
        references [[database.]owner.]ref_table
            [(ref_column [{, ref_column}...])]
    | check (search_condition)}

| drop {[column_name [, column_name]] |
	[constraint constraint_name]}

| modify column_name {[data_type] [null] | 
	[not null]] [, column_name]

| replace column_name
    default {constant_expression | user | null}

| partition number_of_partitions

| unpartition

| lock {allpages | datarows | datapages } }

| with exp_row_size = num_bytes

Usage

Component Integration Services processes the alter table command when the table on which it operates has been created as a proxy table. Component Integration Services forwards the request (or part of it) to the server that owns the actual object.
When Component Integration Services forwards the alter table command to a remote server, it is assumed that the column names on the proxy table and on the remote server are the same.
The only portions of the alter table command that are forwarded to a remote server are add, modify, drop column, partition, and unpartition. The rest of the syntax is processed internally, and not forwarded to a remote server. The only exception to this is the lock clause, and then only for ASEnterprise-class servers.

Server Class ASEnterprise

Component Integration Services forwards the following syntax to a server configured as class sql_server:

alter table [database.[owner].]table_name 
	{add column_name datatype [{identity | null}]
       {[, next_column]}...}
	| [drop column_name [, column_name]}
	| modify column_name [data_type] [NULL] | 
		[not null]] [, column_name]}

When a user adds a column with the alter table command, Component Integration Services passes the datatype of each column to the remote server without type name conversions.
For ASEnterprise class servers only, the lock clause is also forwarded, if contained in the original query, if the version of ASE is 11.9.2 or later.

Server Class ASAnywhere

Handling of the alter table command by servers in this class is the same as for ASEnterprise servers.

Server Class ASIQ

Handling of the alter table command by servers in this class is the same as for ASEnterprise servers.

text and image datatypes are not supported by server class ASIQ. If text and image datatypes are used, Component Integration Services raises Error 11205:
```
Datatype <typename> is unsupported for server <servername>.
```

Server Class sql_server

Handling of the alter table command by servers in this class is the same as for ASEnterprise servers.

Server Class direct_connect

Component Integration Services forwards the following syntax to a remote server configured as class direct_connect:

alter table [database.[owner].]table_name 
 add column_name datatype [{identity | null}]
        {[, next_column]}...

Although Component Integration Services requests a capabilities response from a server with class direct_connect, support for alter table is not optional. Component Integration Services forwards the alter table command to the remote server regardless of the capabilities response.
The behavior of the server with class direct_connect is database dependent.alter table [database .[owner ].]table_name {add column_name datatype [{identity | null}] {[, next_column ]}... The Transact-SQL syntax is forwarded, and errors may or may not be raised, depending on the ability of the remote database to handle this syntax.

If the syntax capability of the remote server indicates Sybase Transact-SQL, Adaptive Server datatypes are sent to the remote server. If the syntax capability indicates DB2 SQL, DB2 datatypes are sent. The mapping for these datatypes is shown in Table 3-2

**Table 3-2: DirectConnect datatype conversions for alter table**
Adaptive Server Datatype	DirectConnect Default Datatype	DirectConnect DB2 Syntax Mode Datatype
binary(n)	binary(n)	char(n) for bit data
bit	bit	char(1)
char	char	char
datetime	datetime	timestamp
decimal(p, s)	decimal(p, s)	decimal(p, s)
float	float	float
image	image	varchar(n) for bit data; the value of n is determined by the global variable @@textsize
int	int	int
money	money	float
numeric(p, s)	numeric(p, s)	decimal(p, s)
nchar(n)	nchar(n)	graphic(n)
nvarchar(n)	nvarchar(n)	vargraphic(n)
real	real	real
smalldatetime	smalldatetime	timestamp
smallint	smallint	smallint
smallmoney	smallmoney	float
timestamp	timestamp	varbinary(8)
tinyint	tinyint	smallint
text	text	varchar(n); the value of n is determined by the global variable @@textsize
unichar	unichar	varchar(n) for bit data
univarchar	univarchar	varchar(n) for bit data
varbinary(n)	varbinary(n)	varchar(n) for bit data
varchar(n)	varchar(n)	varchar(n)

Server Class db2

Component Integration Services forwards the following syntax to a remote server configured as class db2:

alter table [database.[owner].]table_name 
 add column_name datatype [null]
        {[, next_column]}...

text and image datatypes are not supported by server class db2. If text and image datatypes are used, Component Integration Services raises Error 11205:
```
Datatype <typename> is unsupported for server <servername>
```

The datatype specification contains DB2 datatypes that are mapped from Adaptive Server datatypes. The datatype conversions are shown in Table 3-3.

**Table 3-3: DB2 datatype conversions for alter table**
Adaptive Server Datatype	DB2 Datatype
binary(n)	char(n) for bit data, where n <= 254
bit	char(1)
char(n)	char(n), where n <= 254
datetime	timestamp
decimal(p, s)	decimal(p, s)
float	float
image	Not supported
int	int
money	float
nchar	char(n)
nvarchar	varchar(n)
numeric(p, s)	decimal(p, s)
real	real
smalldatetime	timestamp
smallint	smallint
smallmoney	float
tinyint	smallint
text	Not supported
unichar	varchar(n) for bit data
univarchar	varchar(n) for bit data
varbinary(n)	varchar(n) for bit data, where n <=254
varchar(n)	varchar(n), where n <= 254