Home

	Expand Search

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Altering existing tables

Altering existing tables

Use the alter table command to change the structure of an existing table. You can:

• Add columns and constraints

• Change column default values

• Add either null or non-null columns

• Drop columns and constraints

• Change locking scheme

• Partition or unpartition tables

• Convert column datatypes

• Convert the null default value of existing columns

• Increase or decrease column length

alter table includes the following syntax for modifying tables:

alter table table_name
     [add column_name datatype [identity | null | 
          not null] [, column_name datatype [identity 
          |null | not null]]]
     [drop column_name [, column_name]
          [modify column_name {[data_type] 
          [[null] | [not null]]}
          [, column_name datatype [null | not null]]]

Where:

• table_name is the table you are altering.

• datatype is the datatype of the altered column.

You must have the sa_role or be the object owner to run alter table. See the Reference Manual for the complete alter table syntax.

For example, by default, the au_lname column of the authors table uses a varchar(50) datatype. To alter the au_lname to use a varchar(60), enter:

alter table authors 
modify au_lname varchar(60)

A variable cannot be used as the argument to a default that is part of an alter table statement.

Dropping, modifying, and adding non-null columns may perform a data copy, which has implications for required space and the locking scheme. See "Data copying".

The modified table's page chains inherits the table's current configuration options (for example, if fillfactor is set to 50 percent, the new pages have this same fillfactor).

Adaptive Server does partial logging (of page allocations) for alter table operations. However, because alter table is performed as a transaction, you cannot dump the transaction log after running alter table; you must dump the database to ensure it is recoverable. If the server encounters any problems during the alter table operation, Adaptive Server rolls back the transaction.

alter table acquires an exclusive table lock while it is modifying the table schema. This lock is released as soon as the command is done.

alter table does not fire any triggers.

Objects using select * do not list changes to table

Using alter table on remote tables

Adding columns

Adding columns appends column IDs

Adding NOT NULL columns

Adding constraints

Dropping columns

Dropping columns renumbers the column ID

Dropping constraints

Modifying columns

Which datatypes can I convert?

Modifying tables may prevent successful bulk copy of previous dump

Decreasing column length may truncate data

Modifying datetime columns

Modifying the NULL default value of a column

Modifying columns that have precision or scale

Modifying text and image columns

Adding, dropping, and modifying IDENTITY columns

Adding IDENTITY columns

Dropping IDENTITY columns

Modifying IDENTITY columns

Data copying

Changing exp_row_size

Modifying locking schemes and table schema

Altering columns with user defined datatypes

Adding a column with user-defined datatypes

Dropping a column with user-defined datatypes

Modifying a column with user-defined datatypes

Errors and warnings from alter table

Errors and warnings generated by alter table modify

Scripts generated by if exists()...alter table

Renaming tables and other objects

Renaming dependent Objects