ALTER TABLE

Syntax

ALTER TABLE table_path (alter_table_spec [, ...])

alter_table_spec syntax

ALTER COLUMN column_definition [RENAME TO new_column_name]
ADD column_definition
ADD table_constraint
DROP [CONSTRAINT] constraint_name
DROP column_name
RENAME TABLE TO new_table_name

Description

Modifies the table at table_path, which is of the form table_name where the table is in the currently USEd database or database_name.table_name.

Documentation for column_definition and table_constraint can be found on the CREATE TABLE page.

ADD will add columns or constraints to an existing table. DROP will remove them. ALTER will change an existing column to take on different properties.

Note that altering columns between some different types may not work. A more reliable method of altering such columns is to create a new column of the desired type, run an UPDATE command to copy and convert the data to the new column, then drop the old column.

Examples

Database and table for examples:

CREATE DATABASE IF NOT EXISTS alter_table_example;
GO;
USE alter_table_example;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  i UNIQUEIDENTIFIER PRIMARY KEY NOT NULL PARTITION,
  name VARCHAR NOT NULL,
  x FLOAT NOT NULL,
  y FLOAT NOT NULL
);
GO;
INSERT INTO t1 VALUES (NULL, 'Entry One', 10, 10);
INSERT INTO t1 VALUES (NULL, 'Entry Two', 20, 10);
INSERT INTO t1 VALUES (NULL, 'Entry Three', 10, 20);
INSERT INTO t1 VALUES (NULL, 'Entry Four', 20, 20);

Add a new column

ALTER TABLE alter_table_example.t1 (ADD altered DATETIME DEFAULT (GETDATE()));

Add a new constraint

ALTER TABLE alter_table_example.t1 (ADD CONSTRAINT name_key UNIQUE);

Multi-action alter

ALTER TABLE alter_table_example.t1 (
  DROP CONSTRAINT name_key,
  ALTER COLUMN name VARCHAR RENAME TO title,
  ADD CONSTRAINT title_key UNIQUE
);

Transactional Alter

BEGIN TRANSACTION;
ALTER TABLE alter_table_example.t1 (
  ADD x_new DOUBLE,
  ADD y_new DOUBLE
);
UPDATE alter_table_example.t1
  SET x_new=CONVERT(x,DOUBLE)/1000.0,
      y_new=CONVERT(y,DOUBLE)/1000.0;
ALTER TABLE alter_table_example.t1 (
  DROP x FLOAT,
  DROP y FLOAT,
  ALTER COLUMN x_new DOUBLE NOT NULL RENAME TO x,
  ALTER COLUMN y_new DOUBLE NOT NULL RENAME TO y
);
COMMIT;

Related