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, changes the compression level of a table.

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

• Partitions and repartitions a table with specified partition strategy, adds partitions to a table with existing partitions, and splits or merges existing partitions.

Syntax

alter table [[database.][owner].table_name
	{add column_name datatype}
		[default {constant_expression | user | null}]
		{identity | null | not null [not materialized]} 
		[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
				immediate_allocation]
			[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}]]
		[compressed = compression_level | not compressed]
		[, 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]
	| add lob-colname { text | image | unitext }
		[null] [ in row [ (length) ] ]
	| check (search_condition)}
	| set dml_logging = {full | minimal | default} | 
		[, compression = {none | page | row}]}
		[lob_compression = off | compression_level]
	| drop {column_name [, column_name]... 
		| constraint constraint_name}
	| modify column_name 	
		[datatype [null | not null]]
		[[[encrypt [with keyname] [decrypt_default [value]]
			| decrypt
		]
		[[not] compressed]
		[compressed = compression_level | not compressed]
		| modify lob-column [ in row (length)]
				[, 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] 
		| no datacopy}
	| 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] 
			[compression_clause] [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]]...) 
			[compression_clause] [on segment_name]
		| number_of_partitions 
			[on (segment_name[, segment_name] ...)]} 

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

	| partition by roundrobin 
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
			[compression_clause] [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] 
			[compression_clause] [on segment_name]
			[, [partition_name ] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) 
				[on segment_name]]...) 
		| modify partition {partition_name [, partition_name . . .] }
		set compression [= {default | none | row | page}]

		| ([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, splitting, merging, and moving partitions:

alter table table_name 
	drop partition partition_name [, partition_name]...
	split partition partition_name
	merge partition  {partition_name  [{, partition_name}…]} 
		into destination_partition_name [on segment_name]
	move partition partition_name  [{, partition_name}…]
		to destination_segment_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 can be type numeric or integer, and have 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 this example is an existing encrypted column encrypted by “key1” the alter table would cause Adaptive Server to decrypt ssn using “key1” and reencrypt 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

Alters the titles table in the pubs2 database to use row-level compression:

alter table titles set compression = row

Example 16

Changes the Y2009 partition of the sales table to use page-level compression:

alter table sales modify partition Y2009
set compression = page

Example 17

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

alter table titles lock datarows

Example 18

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 19

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

alter table titles
    drop advance, notes, contract

Example 20

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 21

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 22

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 23

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 24

Adds the incremental transfer attribute to mytable:

alter table mytable set transfer table on

Example 25

Removes the incremental transfer attribute from mytable:

alter table mytable set transfer table off

Example 26

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 27

Modifies the description column of mymsgs table to support in-row LOB 400 bytes long:

alter table mymsgs modify description in row (400)

Example 28

Adds a virtual computed column:

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

Example 29

Changes a virtual computed column to a materialized computed column:

alter table authors modify fullname materialized

Example 30

Splits the partition containing the orders table into two partitions:

alter table orders
split partition P2 
into 
( P5 values <= (25000) on seg2, 
  P6 values <= (50000) on seg3)

Example 31

Merges the partitions containing the sales table into a single partition:

alter table sales
merge partition Q1, Q2, Q3, Q4 
into Y2007

Example 32

Moves the orders table to the seg4 segment:

alter table orders
  move partition P2 to seg4

Example 33

Drops the total_sales column from the titles table with a data copy:

alter table titles
drop total_sales
with no datacopy

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 may cause the row size of the table to increase.

• You cannot use alter table to encrypt or decrypt a column:

  • If the column belongs to a clustered or placement index. To encrypt or decrypt such a column, drop the index, alter the column, and re-create the index.

  • If the table has a trigger defined. Drop the trigger before you modify the column. Afterwards, re-create the trigger:

• If you modify the type of an encrypted column belonging to a clustered or placement index, the index is out of order, and alter table displays an error. Drop the index before modifying the type. Afterwards, re-recreate the index.

• You can encrypt these datatypes:

  • 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 datatype of encrypted data on disk is varbinary. Null values are not encrypted.

• Modifying the datatype 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. Drop the index before modifying the type, after which you re-create 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

  • Enable a column for encryption that is already used as a partition key

• Use set compression to change the compression level of the table for future data inserts or updates. set compression does not affect existing data rows and pages that are already compressed, but does require exclusive access to the table.

• You cannot change a partition’s compression level in the same command in which you are altering a table’s compression level. You must perform these operations as independent commands

• You may use set compression with other set parameters.

• Changing the table’s compression level affects only those partitions that do not already have an explicitly defined compression level. All partitions without an explicitly defined compression level implicitly inherit the table’s compression level. For example, if you modify a table’s compression level from uncompressed to row-level compression, all partitions that had a compression level of none do not change, but partitions for which their compression level was undefined are changed to row-level compressed.

• Altering a table’s compression level does not change the compression level for existing columns. For example, if my_table and its columns are uncompressed, when you alter the compression level of my_table, its columns initially remain uncompressed. However, Adaptive Server compresses these columns individually when they fill with enough data to trigger the compression mechanism.

• The default behavior for newly added columns depends on the table’s compression setting. For compressed tables, the column’s datatype determines its compression level. For uncompressed tables, new columns are uncompressed.

• You may add compressed materialized computed columns to a table or compress them later.

When a command requires data movement, Adaptive Server compresses any uncompressed data rows in the source partitions if the target partition is compressed. When you include a compression clause, alter table includes these interactions between the parameters:

• set – you:

  • Cannot combine set with add, drop, or modify clauses.

  • Cannot combine the modify partition set clause with other modify column_name parameters.

  • Cannot use the all keyword with modify partition and include partition names in the clause.

• add:

  • You may add nullable and non-nullable compressed columns to existing tables. Adding non-nullable columns requires data movement.

  • Columns added to a compressed table use row compression if they are configured for an appropriate datatype.

  • Modifying a column’s datatype to one that is eligible for row compression in a compressed table does not change the column’s compression level.

  • If you do not specify not compressed for a new column, it inherits the table’s compression level. For example, if the table is row-level compressed, any column you add to the table uses row-level compression as well.

• drop:

  • Dropping a compressed column causes data movement.

  • If the other columns in a table or partition are not compressed, or cannot be compressed, you must change the compression state to none before dropping the last compressed column.

• modify:

  • You can modify a compressed column in an existing table to not compressed and vice versa.

  • You can change a column’s datatype simultaneously with its compression level. However, the target datatype must be eligible for row compression. If the target datatype is not eligible for compression, Adaptive Server ignores the request to compress it during the modify operation.

  • Adaptive Server issues an error if you attempt to modify the compression level of a column that you cannot create as a compressed column. For example, Adaptive Server issues an error message if you attempt to compress a virtual computed column or an in-row Java column.

• Combining add, drop, and modify:

  • You can issue multiple add, drop, or modify parameters that include one or more compressed columns in a single alter table statement. The data movement for this alter table command is dictated by the data movement restrictions for the parameters.

  • If alter table requires data movement, the column’s compression level remains unchanged for the columns not affected by the add, drop, or modify parameters.

• Repartitioning a table – if you do not specify the compression clause for new partitions, Adaptive Server sets the compression level for the new partitions as:

  • Uncompressed if the source table and all of its partitions are uncompressed.

  • The same compression level as the source table, if all of its partitions are compressed with the same compression level.

  • Uncompressed if:

    • The table or the individual partitions are compressed differently, or,

    • The source table is not compressed, but some of its partitions are

    Adaptive Server does not compress the new partitions because it may be difficult to uniquely map and migrate the compression attribute from the original compressed partitions to the new partitions. You must explicitly state the compression level as part of the alter table command to specify which target partitions must be compressed during the data copy.

• add partition – newly added partitions inherit the table’s compression level if you do not specify a partition level in the compression clause.

• drop partition – dropping a table’s only compressed partition does not change the table’s compression level if the table includes multiple partitions.

  If a table has been defined for compression, it remains compressed after the partition is dropped, and Adaptive Server automatically configures future partitions for compression.

• Changing the locking scheme – Adaptive Server requires data movement if you change the locking scheme of a table from allpages-locked to data-only-locked, or vice-versa. You cannot simultaneously change the compression level of the table or individual partitions when you change the locking scheme. Instead, you must run the set compression command to specify the compression level before you change the locking scheme.

• Unpartitioning a table – if at least one source partition is compressed when you unpartition a table, the entire table is marked as compressed when you run alter table. Adaptive Server issues a warning message if the table was initially uncompressed.

• Other commands – you cannot combine parameters that specify the default value of a column, enable or disable triggers, add or drop column-level or table-level constraints, and so on, with commands that specify column-level or partition-level compression or copy data.

• If alter table does not include data movement, existing data is not affected, and Adaptive Server applies the table’s compression level to data once it is inserted. If alter table includes data movement, existing data is compressed or decompressed according to the table’s compression level.

• These alter table events include data movement:

  • Adding non-null columns

  • Dropping columns

  • Modifying a column to increase its length (for example, from a smallint to an int, or from char(3) to varchar(45))

• These alter table events do not include data movement:

  • Adding null column

  • Adding null text column (adding a non-null text column has restrictions)

  • Modifying a variable-length column to increase its length (for example, from varchar(5) to varchar(20) or from varchar(20) to varchar(40))

• You cannot compress proxy tables or partitions or columns on proxy tables.

• Changing the table’s large object (LOB) compression level affects only the LOB columns that do not have an explicitly defined compression level. Columns without an explicitly defined compression level implicitly inherit the table’s compression level.

• The default behavior for newly added LOB columns for which you have not specified a LOB compression clause depends on the table’s LOB compression level. For LOB compressed tables, Adaptive Server uses the table’s LOB compression level for the columns. For LOB uncompressed tables, newly added LOB columns remain uncompressed.

Interactions between compression and other alter table parameters for tables with LOB data:

• drop column – if the table includes no compressed LOB columns after dropping columns, the table uses the table-level LOB column compression level.

• add column

  • You can add a nullable compressed LOB column, but you cannot add a non-nullable compressed LOB column.

  • For a table not set to LOB compression, by default, newly added LOB columns are not compressed. Newly added LOB columns with LOB compression subclauses can be compressed or not compressed as specified.

• modify column

  • You can uncompress an existing compressed LOB column. Although newly inserted data is uncompressed, existing data remains compressed.

  • You can change the compression level of an existing LOB column. Although newly inserted data assumes the new compression level, existing data retains the original compression level.

  • You can change an uncompressed LOB column to compressed.

  • You cannot modify a regular column to a LOB column (compressed or uncompressed).

  • You can modify a compressed LOB column to:

    • Compressed text columns using nchar, nvarchar, unichar, and univarchar

    • Compressed image columns using varbinary and binary

    • Compressed unitext columns using nchar, nvarchar, unichar, univarchar, varbinary, and binary

    Compressed off-row java columns cannot be modified to regular columns.

    Adaptive Server decompresses the LOB data, truncating the data if necessary to fit the regular column length, and converting it to the regular datatype. The maximum length of the regular column is governed by the Adaptive Server page size.

• Combinations of add, drop, modify, and set lob_compression:

  • You can issue multiple add, drop, or modify subcommands in a single alter table command—or set lob_compression and set compression subclauses—that involves one or more compressed columns.

  • If you add a column to a LOB-compressed table and include set lob_compression = 0 in the command, the newly added column is not compressed.

  • If you add a column to a regular, uncompressed table, and include set lob_compression = compression_level in the command, the newly added column is compressed.

  Existing LOB data is not affected by alter table commands; only future DMLs are affected by the changed LOB compression attributes. Use update and select into to compress or uncompress existing LOB data.

• 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. See “Indexing for Performance” in the Performance and Tuning Guide: Basics.

• You can create column defaults either 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. First use sp_unbindefault to unbind the default.

• 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 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 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, modify, and lock subclauses 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, 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.

• A 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 CIS. See the Component Integration Services Users 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 allpages-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 Users Guide.

• 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 Users 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 errors. 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 table 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.