alter table

Description

Adds new columns to a table; drops or modifies existing columns; adds, changes, or drops constraints; changes properties of an existing table; enables or disables triggers on a table.

Syntax

alter table [[database.][owner].table_name

	{ add column_name datatype
		[default {constant_expression | user | null}]
		{identity | null | not null} 
		[off row | in row]
		[ [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 datatype [null | not null] 
		[, next_column]...

	| replace column_name
		default { constant_expression | user | null}

	| partition number_of_partitions

	| unpartition

	| { enable | disable } trigger

	| lock {allpages | datarows | datapages } }

	| with exp_row_size=num_bytes

Parameters

table_name

is the name of the table to change. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

add

specifies the name of the column or constraint to add to the table. If Component Integration Services is enabled, you cannot use add for remote servers.

column_name

is the name of a column in that table. If Java is enabled in the database, the column can be a Java-SQL column.

datatype

is any system datatype except bit or any user-defined datatype except those based on bit.

If Java is enabled in the database, can be the name of a Java class installed in the database, either a system class or a user-defined class. Refer to Java in Adaptive Server Enterprise for more information.

default

specifies a default value for a column. If you specify a default and the user does not provide a value for this column when inserting data, Adaptive Server inserts this value. The default can be a constant_expression, user (to insert the name of the user who is inserting the data), or null (to insert the null value).

Adaptive Server generates a name for the default in the form of tabname_colname_objid, where tabname is the first 10 characters of the table name, colname is the first 5 characters of the column name, and objid is the object ID number for the default. Setting the default to null drops the default.

If Component Integration Services is enabled, you cannot use default for remote servers.

constant_expression

is a constant expression to use as a default value for a column. It cannot include global variables, the name of any columns, or other database objects, but can include built-in functions. This default value must be compatible with the datatype of the column.

user

specifies that Adaptive Server should insert the user name as the default if the user does not supply a value. The datatype of the column must be either char(30), varchar(30), or a type that Adaptive Server implicitly converts to char; however, if the datatype is not char(30) or varchar(30), truncation may occur.

null | not null

specifies Adaptive Server’s behavior during data insertion if no default exists.

null specifies that a column is added that allows nulls. Adaptive Server assigns a null value during inserts if a user does not provide a value.

not null specifies that a column is added that does not allow nulls. Users must provide a non-null value during inserts if no default exists.

If you do not specify null or not null, Adaptive Server uses not null by default. However, you can switch this default using sp_dboption to make the default compatible with the SQL standards. If you specify (or imply) not null for the newly added column, a default clause is required. The default value is used for all existing rows of the newly added column, and applies to future inserts as well.

identity

indicates that the column has the IDENTITY property. Each table in a database can have one IDENTITY column of type numeric and scale zero. IDENTITY columns are not updatable and do not allow nulls.

IDENTITY columns store sequential numbers, such as invoice numbers or employee numbers, automatically generated by Adaptive Server. The value of the IDENTITY column uniquely identifies each row in a table.

If Component Integration Services is enabled, you cannot use identity for remote servers.

off row | in row

specifies whether the Java-SQL column is stored separate from the row or in storage allocated directly in the row.

The storage for an in row column must not exceed 16K bytes, depending on the page size of the database server and other variables. The default value is off row.

constraint

introduces the name of an integrity constraint. If Component Integration Services is enabled, you cannot use constraint for remote servers.

constraint_name

is the name of the constraint. It must conform to the rules for identifiers and be unique in the database. If you do not specify the name for a table-level constraint, Adaptive Server generates a name in the form of tabname_colname_objectid, where tabname is the first 10 characters of the table name, colname is the first 5 characters of the column name, and objectid is the object ID number for the constraint. If you do not specify the name for a unique or primary key constraint, Adaptive Server generates a name in the format tabname_colname_tabindid, where tabindid is a string concatenation of the table ID and index ID.

Constraints do not apply to the data that already exists in the table at the time the constraint is added.

unique

constrains the values in the indicated column or columns so that no two rows can have the same non-null value. This constraint creates a unique index that can be dropped only if the constraint is dropped. You cannot use this option along with the null option described above.

primary key

constrains the values in the indicated column or columns so that no two rows can have the same value and so that the value cannot be NULL. This constraint creates a unique index that can be dropped only if the constraint is dropped.

clustered | nonclustered

specifies that the index created by a unique or primary key constraint is a clustered or nonclustered index. clustered is the default (unless a clustered index already exists for the table) for primary key constraints; nonclustered is the default for unique constraints. There can be only one clustered index per table. See create index for more information.

asc | desc

specifies whether the index is to be created in ascending (asc) or descending (desc) order. The default is ascending order.

with fillfactor=pct

specifies how full to make each page when Adaptive Server creates a new index on existing data. “pct” stands for percentage. The fillfactor percentage is relevant only when the index is created. As the data changes, the pages are not maintained at any particular level of fullness.

WARNING!  Creating a clustered index with a fillfactor affects the amount of storage space your data occupies, since Adaptive Server redistributes the data as it creates the clustered index.

The default for fillfactor is 0; this is used when you do not include with fillfactor in the create index statement (unless the value has been changed with sp_configure). When specifying a fillfactor, use a value between 1 and 100.

A fillfactor of 0 creates clustered indexes with completely full pages and nonclustered indexes with completely full leaf pages. It leaves a comfortable amount of space within the index B-tree in both clustered and nonclustered indexes. There is seldom a reason to change the fillfactor.

If the fillfactor is set to 100, Adaptive Server creates both clustered and nonclustered indexes with each page 100 percent full. A fillfactor of 100 makes sense only for read-only tables—tables to which no additional data will ever be added.

fillfactor values smaller than 100 (except 0, which is a special case) cause Adaptive Server to create new indexes with pages that are not completely full. A fillfactor of 10 might be a reasonable choice if you are creating an index on a table that will eventually hold a great deal more data, but small fillfactor values cause each index (or index and data) to take more storage space.

max_rows_per_page=num_rows

limits the number of rows on data pages and the leaf level pages of indexes. Unlike fillfactor, the max_rows_per_page value is maintained until it is changed with sp_chgattribute.

If you do not specify a value for max_rows_per_page, Adaptive Server uses a value of 0 when creating the index. When specifying max_rows_per_page for data pages, use a value between 0 and 256. The maximum number of rows per page for nonclustered indexes depends on the size of the index key; Adaptive Server returns an error message if the specified value is too high.

For indexes created by constraints, a max_rows_per_page setting of 0 creates clustered indexes with full pages and nonclustered indexes with full leaf pages. A setting of 0 leaves a comfortable amount of space within the index B-tree in both clustered and nonclustered indexes.

If max_rows_per_page is set to 1, Adaptive Server creates both clustered and nonclustered leaf index pages with one row per page at the leaf level. You can use this to reduce lock contention on frequently accessed data.

Low max_rows_per_page values cause Adaptive Server to create new indexes with pages that are not completely full, use more storage space, and may cause more page splits.

WARNING!  Creating a clustered index with max_rows_per_page can affect the amount of storage space your data occupies, since Adaptive Server redistributes the data as it creates the clustered index.

reservepagegap = num_pages

specifies a ratio of filled pages to empty pages to be left during extent I/O allocation operations for the index created by the constraint. For each specified num_pages, an empty page is left for future expansion of the table. Valid values are 0 – 255. The default value, 0, leaves no empty pages.

on segment_name

specifies that the index is to be created on the named segment. Before the on segment_name option can be used, the device must be initialized with disk init, and the segment must be added to the database with the sp_addsegment system procedure. See your System Administrator or use sp_helpsegment for a list of the segment names available in your database.

If you specify clustered and use the on segment_name option, the entire table migrates to the segment you specify, since the leaf level of the index contains the actual data pages.

references

specifies a column list for a referential integrity constraint. You can specify only one column value for a column-constraint. By including this constraint with a table that references another table, any data inserted into the referencing table must already exist in the referenced table.

To use this constraint, you must have references permission on the referenced table. The specified columns in the referenced table must be constrained by a unique index (created by either a unique constraint or a create index statement). If no columns are specified, there must be a primary key constraint on the appropriate columns in the referenced table. Also, the datatypes of the referencing table columns must exactly match the datatype of the referenced table columns.

If Component Integration Services is enabled, you cannot use references for remote servers.

foreign key

specifies that the listed column(s) are foreign keys in this table whose matching primary keys are the columns listed in the references clause.

ref_table

is the name of the table that contains the referenced columns. You can reference tables in another database. Constraints can reference up to 192 user tables and internally generated worktables. Use the system procedure sp_helpconstraint to check a table’s referential constraints.

ref_column

is the name of the column or columns in the referenced table.

check

specifies a search_condition constraint that Adaptive Server enforces for all the rows in the table. If Component Integration Services is enabled, you cannot use check for remote servers.

search_condition

is a boolean expression that defines the check constraint on the column values. These constraints can include:

next_column

includes additional column definitions (separated by commas) using the same syntax described for a column definition.

drop

specifies the name of a column or constraint to drop from the table. If Component Integration Services is enabled, you cannot use drop for remote servers.

modify

specifies the name of the column whose datatype or nullability you are changing.

replace

specifies the column whose default value you want to change with the new value specified by a following default clause. If Component Integration Services is enabled, you cannot use replace for remote servers.

partition number_of_partitions

creates multiple database page chains for the table. Adaptive Server can perform concurrent insertion operations into the last page of each chain. number_of_partitions must be a positive integer greater than or equal to 2. Each partition requires an additional control page; lack of disk space can limit the number of partitions you can create in a table. Lack of memory can limit the number of partitioned tables you can access. If Component Integration Services is enabled, you cannot use partition for remote servers.

unpartition

creates a single page chain for the table by concatenating subsequent page chains with the first one. If Component Integration Services is enabled, you cannot use unpartition for remote servers.

enable | disable trigger

Enables or disables a trigger. For more information, see the System Administration Guide.

lock datarows | datapages | allpages

changes the locking scheme to be used for the table.

with exp_row_size=num_bytes

specifies the expected row size. Applies only to datarows and datapages locking schemes, to tables with variable-length rows, and only when alter table performs a data copy. Valid values are 0, 1, and any value between the minimum and maximum row length for the table. The default value is 0, which means a server-wide setting is applied.

Examples

Example 1

Adds a column to a table. For each existing row in the table, Adaptive Server assigns a NULL column value:

alter table publishers 
add manager_name varchar(40) null

Example 2

Adds an IDENTITY column to a table. For each existing row in the table, Adaptive Server assigns a unique, sequential column value. Note that the IDENTITY column has type numeric and a scale of zero. The precision determines the maximum value (10 5 - 1, or 99,999) that can be inserted into the column:

alter table sales_daily
add ord_num numeric(5,0) identity

Example 3

Adds a primary key constraint to the authors table. If there is an existing primary key or unique constraint on the table, the existing constraint must be dropped first (see Example 5):

alter table authors
add constraint au_identification
primary key (au_id, au_lname, au_fname)

Example 4

Creates an index on authors; the index has a reservepagegap value of 16, leaving 1 empty page in the index for each 15 allocated pages:

alter table authors
add constraint au_identification
primary key (au_id, au_lname, au_fname)
with reservepagegap = 16

Example 5

Drops the au_identification constraint:

alter table titles 
drop constraint au_identification

Example 6

Removes the default constraint on the phone column in the authors table. If the column allows NULL values, NULL is inserted if no column value is specified. If the column does not allow NULL values, an insert that does not specify a column value fails:

alter table authors
replace phone default null

Example 7

Creates four new page chains for the titleauthor table. After the table is partitioned, existing data remains in the first partition. New rows, however, are inserted into all five partitions:

alter table titleauthor partition 5

Example 8

Concatenates all page chains of the titleauthor table, then repartitions it with six partitions:

alter table titleauthor unpartition
alter table titleauthor partition 6

Example 9

Changes the locking scheme for the titles table to datarows locking:

alter table titles lock datarows

Example 10

Adds the not-null column author_type to the authors table with a default of primary_author:

alter table authors
    add author_type varchar(20)
    default "primary_author" not null

Example 11

Drops the advance, notes, and contract columns from the titles table:

alter table titles
    drop advance, notes, contract

Example 12

Modifies the city column of the authors table to be a varchar(30) with a default of NULL:

alter table authors
    modify city varchar(30) null

Example 13

Modifies the stor_name column of the stores table to be NOT NULL. Note that its datatype, varchar(40), remains unchanged:

alter table stores
    modify stor_name not null

Example 14

Modifies the type column of the titles table and changes the locking scheme of the titles table from allpages to datarows:

alter table titles
    modify type varchar(10)
    lock datarows

Example 15

Modifies the notes column of the titles table from varchar(200) to varchar(150), changes the default value from NULL to NOT NULL, and specifies an exp_row_size of 40:

alter table titles
    modify notes varchar(150) not null
    with exp_row_size = 40

Example 16

Adds, modifies, and drops a column, and then adds another column in one query. Alters the locking scheme and specifies the exp_row_size of the new column:

alter table titles
    add author_type varchar(30) null
    modify city varchar(30)
    drop notes
    add sec_advance money default 1000 not null
    lock datarows
    with exp_row_size = 40

Usage


Restrictions

WARNING! Do not alter the system tables.


Getting information about tables


Specifying ascending or descending ordering in indexes


Using cross-database referential integrity constraints


Changing defaults


Setting space management properties for indexes


Conversion of max_rows_per_page to exp_row_size


Using reservepagegap


Partitioning tables for improved insert performance


Adding IDENTITY columns


Altering table schema


Restrictions for modifying a table schema


Restrictions for modifying text and image columns


Changing locking schemes


Adding Java-SQL columns

Standards

SQL92 – Compliance level: Transact-SQL extension.

See Chapter 1, “System and User-Defined Datatypes” for datatype compliance information.

Permissions

alter table permission defaults to the table owner; it cannot be transferred except to the Database Owner, who can impersonate the table owner by running the setuser command. A System Administrator can also alter user tables.

See also

Commands create index, create table, dbcc, drop database, dump transaction, insert, setuser

System procedures sp_chgattribute, sp_help, sp_helpartition, sp_rename