alter table

Makes changes to existing tables.

  • 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 | ALL} ] 
           | [,index_compression = {NONE | PAGE} ]
           | [,"erase residual data" (on | off) ]
          }
		[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}]
        set index_compression [= {none | 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

Usage

See also sp_chgattribute, sp_help, sp_helpartition, sp_rename in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

See System and User-Defined Datatypes in Reference Manual: Building Blocks for datatype compliance information.

Permissions

The permission checks for alter table differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the table owner or a user with alter any table privilege. A user with setuser privilege can impersonate the table owner by executing the setuser command.

Disabled

With granular permissions disabled, you must be the table owner or a user with sa_role. The database owner can impersonate the table owner by running the setuser command

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

3

Audit option

alter

Command or access audited

alter table

Information in extrainfo
  • Roles – current active roles

  • Keywords or optionsadd column, drop column, modify column, replace column, add constraint, or drop constraint

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if a set proxy is in effect

  • If the set option for set transfer table [on | off] is:
    • on – the SAP ASE server prints SET TRANSFER TABLE ON in the extra info in the audit record.

    • off – the SAP ASE server prints SET TRANSFER TABLE OFF.

Related reference
create index
create table
dbcc
drop database
dump transaction
insert
setuser
select