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.

• Supports adding, dropping, and modifying computed columns and to enable the materialized property, nullability, or definition of an existing computed column to be changed.

• Partitions and repartitions a table with specified partition strategy, or add partitions to a table with existing partitions. Syntax for altering table partitions is listed separately. See the alter table syntax for partitions.

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)]
			[match full]
		| check (search_condition)]
		[encrypt [with [database.[owner].] keyname]
			[decrypt_default {constant_expression | null}]]
		[, 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}...])]
		[match full]
	| check (search_condition)}
	| set dml_logging = {full | minimal | default}
	| drop {column_name [, column_name]... 
		| constraint constraint_name}
	| modify column_name 	
		[datatype [null | not null]]
		[[[encrypt [with keyname] [decrypt_default [value]]
			| decrypt
		]
				[, next_column]...
	| replace column_name
		default {constant_expression | user | null}
		| decrypt_default {constant_expression | null}
		| drop decrypt_default	|
	lock {allpages | datarows | datapages} }
	| with exp_row_size=num_bytes
		transfer table [on | off]}
	| partition number_of_partitions
	| unpartition
	| partition_clause
	| add_partition_clause

alter table syntax for partitions:

partition_clause::=
	partition by range (column_name[, column_name]...) 
		 ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}] ...) [on segment_name] 
			[, [partition_name] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) [on segment_name]]...)

	| partition by hash (column_name[, column_name]...)
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name[, segment_name] ...)]} 

	| partition by list (column_name) 
		 ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)

	| partition by roundrobin 
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name [, segment_name]...)]}

add_partition_clause::=
	add partition 
		{ ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}]...) 
			[on segment_name] 
			[, [partition_name ] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) 
				[on segment_name]]...) 

		| ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)} 

alter table syntax for computed columns

alter table 
	add column_name {compute | as}
		computed_column_expression...
			[materialized | not materialized]
	drop column_name 
	modify column_name {null | not null | 
		{materialized | not materialized} [null | not null] | 
		{compute | as} computed_column_expression 
			[materialized | not materialized]
			[null | not null]}

alter table syntax for dropping partitions

alter table table_name drop partition
	partition_name [, partition_name]...

Parameters

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. The IDENTITY column could be type numeric or integer, and a scale of zero. The precision determines the maximum value (10 ⁵ -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, you must drop the existing constraint first (see next example):

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

Example 4

Drops the au_identification constraint:

alter table titles 
    drop constraint au_identification

Example 5

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 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

Modifies the emp table to encrypt the ssn column and specifies decrypt default:

alter table emp modify ssn encrypt with key1 
    decrypt_default '000-00-0000'

Example 8

Decrypts credit card data that is longer sensitive:

alter table stolen_ccards
     modify ccard decrypt

If card was encrypted by a key protected by a user-defined password, precede this command with the set encryption key command.

Example 9

Adds an encrypted column to an existing table. Because keyname is omitted, Adaptive Server looks for the database default encryption key:

alter table sales_mgr
     add bonus money null encrypt

Example 10

Sets the password for the ssn_key encryption key and encrypts the ssn column in the existing employee table.

set encryption passwd '4evermore' for key ssn_key
alter table employee modify ssn
         encrypt with ssn_key

If ssn in the above example were an existing encrypted column encrypted by ‘key1’ the alter table would cause Adaptive Server to decrypt ssn using ‘key1’ and re-encrypt ssn using ‘ssn_key’.

Example 11

Adds a decrypt default to the salary column, which is already encrypted:

alter table employee replace salary 
    decrypt_default $0.00

Example 12

Removes the decrypt default for salary without removing the encryption property:

alter table employee replace salary drop 
    decrypt_default

Example 13

Changes an unpartitioned table to a range-partitioned table with three partitions, each of which is on a different segment:

alter table titles partition by range (total_sales)
    (smallsales values <= (500) on seg1,
    mediumsales values <= (5000) on seg2,
    bigsales values <= (25000) on seg3)

Example 14

Adds another range partition to the titles table:

alter table titles add partition 
     (vbigsales values <= (40000) on seg4)

Example 15

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

alter table titles lock datarows

Example 16

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 17

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

alter table titles
    drop advance, notes, contract

Example 18

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 19

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

alter table stores
    modify stor_name not null

Example 20

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 21

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 22

Adds the incremental transfer attribute to mytable:

alter table mytable set transfer table on

Example 23

Removes the incremental transfer attribute from mytable:

alter table mytable set transfer table off

Example 24

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

Example 25

Add a virtual computed column:

alter table authors 
    add fullname compute au_fname + ' ' + au_lname

Example 26

Changes a virtual computed column to a materialized computed column:

alter table authors modify fullname materialized

Usage

• When used to add or modify an encrypted column, alter table may take a significant amount of time if the table contains a large number of rows.

• Modifying a column for encryption can cause the row size of the table to increase.

• You cannot use alter table to encrypt or decrypt a column belonging to a clustered or placement index. To encrypt or decrypt such a column:

  1. Drop the index.

  2. Alter the column.

  3. Re-create the index.

• You cannot use alter table to encrypt or decrypt a column if the table has a trigger defined. To modify the column:

  1. Drop the trigger.

  2. Alter the column.

  3. Re-create the trigger.

• If the type of the encrypted column which belongs to a clustered or placement index is modified, it results in the index being out of order. alter table displays an error. To modify the type:

  1. Drop the index

  2. Modify the type

  3. Re-create the index

• You can encrypt these data types:

  • int, smallint, tinyint

  • unsigned int, unsigned smallint, unsigned tinyint

  • bigint, unsigned bigint

  • decimal and numeric

  • float4 and float8

  • money, smallmoney

  • date, time, smalldatetime, datetime, bigdatetime

  • char and varchar

  • unichar, univarchar

  • binary and varbinary

  • bit

• The underlying data type of encrypted data on disk is varbinary. Null values are not encrypted.

• Modifying the data type of the encrypted column belonging to a clustered or placement index results in the index being out of order, and alter table displays an error. To modify the type:

  1. Drop the index

  2. Modify the type

  3. Recreate the index

• alter table reports an error if you:

  • Change a computed column to an encrypted column or change an encrypted column to a computed column

  • Enable a column for encryption where the column is referenced in an expression used by a computed column

  • Change a computed column to reference an encrypted column.

  • Encrypt a column that is a member of a functional index

  • Specify an encrypted column as a partition key

  • Encrypt a column that is already used as a partition key

• For information about a table and its columns, use sp_help.

• To rename a table, execute sp_rename (do not rename the system tables).

• For information about integrity constraints (unique, primary key, references, and check) or the default clause, see create table in this chapter.

• Use the asc and desc keywords after index column names to specify the sort order for the index. Creating indexes so that columns are in the same order specified in the order by clause of queries eliminates the sorting step during query processing. For more information, see Chapter 13, “Indexing for Performance” in the Performance and Tuning Guide: Basics.

• You can create column defaults in two ways: by declaring the default as a column constraint in the create table or alter table statement, or by creating the default using the create default statement and binding it to a column using sp_bindefault.

• You cannot replace a user-defined default bound to the column with sp_bindefault. Unbind the default with sp_unbindefault first.

• If you declare a default column value with create table or alter table, you cannot bind a default to that column with sp_bindefault. Drop the default by altering it to NULL, then bind the user-defined default. Changing the default to NULL unbinds the default and deletes it from the sysobjects table.

• If a table has max_rows_per_page set, and the table is converted from allpages locking to data-only locking, the value is converted to an exp_row_size value before the alter table...lock command copies the table to its new location. The exp_row_size is enforced during the copy. The following table shows how the values are converted.

  If max_rows_per_page is set to	Set exp_row_size to
  0	Percentage value set by default exp_row_size percent
  255	1, that is, fully packed pages
  1–254	The smaller of: Maximum row size 2002/max_rows_per_page value

• Commands that use large amounts of space allocate new space by allocating an extent rather than allocating single pages. The reservepagegap keyword causes these commands to leave empty pages so that future page allocations take place close to the page that is being split or to the page from which a row is being forwarded.

• The reservepagegap value for a table is stored in sysindexes, and is applied when the locking scheme for a table is changed from allpages locking to data-only locking or vice versa. To change the stored value, use sp_chgattribute before running alter table.

• reservepagegap specified with the clustered keyword on an allpages-locked table overwrites any value previously specified with create table or alter table.

• When you add a new computed column without specifying nullability and the materialization property, the default option is nullable and not materialized.

• When you add a new materialized computed column, the computed_column_expression is evaluated for each existing row in the table, and the result is stored in the table.

• You cannot add new computed columns and add or modify their base columns at the same time.

• You can modify the entire definition of an existing computed column. This is a quick way to drop the computed column and add a new one with the same name. Such a column behaves like a new computed column: its defaults are not materialized and nullable, if you do not specify these options.

• You can modify the materialization property of an existing computed column without changing its other properties, such as the expression that defines it or its nullability.

• When you modify a not-null, materialized computed column into a virtual column, you must specify “null” in the modify clause.

• When you modify a computed column that is not materialized, to materialize it, the computed_column_expression is evaluated for each existing row in the table, and the result is stored in the table.

• If you modify existing columns that are index keys, the index is rebuilt.

• You cannot modify a materialized computed column into a virtual column if it has been used as an index key; you must first drop the index.

• You cannot modify a regular column to become a computed column, or a computed column to become a regular column.

• You cannot modify or drop the base column referenced by a computed column.

• You cannot drop a computed column if it is used as an index key.

• When adding a numeric or integer IDENTITY column to a table, make sure the column precision is large enough to accommodate the number of existing rows. If the number of rows exceeds 10 ^precision - 1, Adaptive Server prints an error message and does not add the column.

• When adding an IDENTITY column to a table, Adaptive Server:

  • Locks the table until all the IDENTITY column values have been generated. If a table contains a large number of rows, this process may be time-consuming.

  • Assigns each existing row a unique, sequential IDENTITY column value, beginning with the value 1.

  • Logs each insert operation into the table. Use dump transaction to clear the database’s transaction log before adding an IDENTITY column to a table with a large number of rows.

• Each time you insert a row into the table, Adaptive Server generates an IDENTITY column value that is one higher than the last value. This value takes precedence over any defaults declared for the column in the alter table statement or bound to it with sp_bindefault.

• add, drop, or modify, and lock sub-clauses are useful in changing an existing table’s schema. A single statement can contain any number of these sub-clauses, in any order, as long as the same column name is not referenced more than once in the statement.

• If stored procedures using select * reference a table that has been altered, no new columns appear in the result set, even if you use the with recompile option. You must drop the procedure and re-create it to include these new columns.

• To ensure that triggers fire properly, you must drop and re-create all triggers on an altered table after you perform an add, drop, modify, or lock operation.

• Adaptive Server issues an error message if you add a not null column with alter table.

• You cannot drop all the columns in a table. Also, you cannot drop the last remaining column from a table (for example, if you drop four columns from a five-column table, you cannot then drop the remaining column). To remove a table from the database, use drop table.

• Data copy is required:

  • To drop a column

  • To add a NOT NULL column

  • For most alter table ... modify commands

  Use set noexec on and showplan on options to determine if a data copy is required for a particular alter table command.

• You can specify a change in the locking scheme for the modified table with other alter table commands (add, drop, or modify) when the other alter table command requires a data copy.

• If alter table performs a data copy, select into /bulkcopy/pllsort must be turned on in the database that includes the table whose schema you are changing.

• The modified table retains the existing space management properties (max_rows_per_page, fillfactor, and so on) and indexes of the table.

• alter table that requires a data copy does not fire any triggers.

• You can use alter table to change the schema of remote proxy tables created and maintained by Component Integration Services (CIS). For information about CIS, see the Component Integration Services User’s Guide.

• You cannot perform a data copy and add a table level or referential integrity constraint in the same statement.

• You cannot perform a data copy and create a clustered index in the same statement.

• If you add a not null column, you must also specify a default clause. This rule has one exception: if you add a user-defined type column, and the type has a default bound to it, you need not specify a default clause.

• You can always add, drop, or modify a column in all-pages locked tables. However, there are restrictions for adding, dropping, or modifying a column in a data-only locked table, which are described in the following table:

  Type of index	All pages locked, partitioned table	All pages locked, unpartitioned table	Data-only locked, partitioned table	Data-only locked, unpartitioned table
  Clustered	Yes	Yes	No	Yes
  Nonclustered	Yes	Yes	Yes	Yes

  If you need to add, drop, or modify a column in a data-only-locked table partitioned with a clustered index, you can:

  1. Drop the clustered index.

  2. Alter the data-only locked table.

  3. Re-create the clustered index.

• You cannot add a NOT NULL Java object as a column. By default, all Java columns always have a default value of NULL, and are stored as either varbinary strings or as image datatypes.

• You cannot modify a partitioned table that contains a Java column if the modification requires a data copy. Instead, first unpartition the table, execute alter table, then repartition the table.

• You cannot drop the key column from an index or a referential integrity constraint. To drop a key column, first drop the index or referential integrity constraint, then drop the key column. See the Transact-SQL User’s Guide for more information.

• You can drop columns that have defaults or rules bound to them. Any column-specific defaults are also dropped when you drop the column. You cannot drop columns that have check constraints or referential constraints bound to them. Instead, first drop the check constraint or referential constraint, then drop the column. Use sp_helpconstraint to identify any constraints on a table, and use sp_depends to identify any column-level dependencies.

• You cannot drop a column from a system table. Also, you cannot drop columns from user tables that are created and used by Sybase-provided tools and stored procedures.

• You can generally modify the datatype of an existing column to any other datatype if the table is empty. If the table is not empty, you can modify the datatype to any datatype that is explicitly convertible to the original datatype.

• You can:

  • Add a new IDENTITY column.

  • Drop an existing IDENTITY column.

  • Modify the size of an existing IDENTITY.

  See the Transact-SQL User’s Guide for more information.

• Altering the schema of a table increments the schema count, causing existing stored procedures that access this table to be renormalized the next time they are executed. Changes in datatype-dependent stored procedures or views may fail with datatype normalization type errors. You must update these dependent objects so they refer to the modified schema of the table.

• You cannot run alter table from inside a transaction.

• Altering a table’s schema can invalidate backups that you made using bcp. These backups may use a tables schema that is no longer compatible with the table’s current schema.

• You can add NOT NULL columns with check constraints, however, Adaptive Server does not validate the constraint against existing data.

• You cannot change the locking scheme of a table using the alter table . . . add, drop, or modify commands if the table has a clustered index and the operation requires a data copy. Instead you can

  1. Drop the clustered index.

  2. Alter the table’s schema.

  3. Re-create the clustered index.

• You cannot alter a table’s schema if there are any active open cursors on the table.

• You can only add text or image columns that accept null values.

  To add a text or image column so it contains only non-null values, first add a column that only accepts null values and then update it to the non-null values.

• You can modify a column from text datatype only to the following datatypes:

  • [n]char

  • [n]varchar

  • unichar

  • univarchar

  • nchar

  • nvarchar

• You can modify a column from image datatype only to binary or varbinary.

• You cannot add a new text or image column and then drop an existing text or image column in the same statement.

• You cannot modify a column to either text or image datatype.

The following restrictions apply when you use alter table to modify unitext columns:

• You can add a new unitext column that accepts NULL values.

• You can modify a column from unitext only to the following datatypes:

  • [n]char

  • [n]varchar

  • unichar

  • univarchar

  • binary

  • varbinary

• You cannot modify a column to the unitext datatype.

• You cannot add a unitext column and drop an existing unitext column in the same statement.

• A referential integrity constraint cannot reference a column on a local temporary database except from a table on the same local temporary database. alter table fails when it attempts to create a reference to a column on a local temporary database from a table in another database.

• You cannot encrypt a column with an encryption key stored in a local temporary database unless the column’s table resides on the same local temporary database. alter table fails if it attempts to encrypt a column with an encryption key on the local temporary database and the table is in another database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.