Session9 - SQL Server - ALTER Command

Показать описание

The ALTER TABLE statement is used to modify the structure of an existing table in SQL Server. It can be used to add, delete, or modify columns, constraints, indexes, and other table properties.

The general syntax for the ALTER TABLE statement is:

ALTER TABLE table_name
[alter_option [,...n] ]
;

The alter_option can be one of the following:

ADD: Adds a new column to the table.
DROP: Deletes an existing column from the table.
CHANGE: Changes the definition of an existing column.
MODIFY: Changes the data type of an existing column.
RENAME: Changes the name of an existing column.
ADD CONSTRAINT: Adds a new constraint to the table.
DROP CONSTRAINT: Deletes an existing constraint from the table.
ADD INDEX: Adds a new index to the table.
DROP INDEX: Deletes an existing index from the table.
REBUILD INDEX: Rebuilds an existing index.
REORGANIZE INDEX: Reorganizes an existing index.
DISABLE TRIGGER: Disables an existing trigger.
ENABLE TRIGGER: Enables an existing trigger.

For example, the following statement adds a new column to the Customers table:
ALTER TABLE Customers
ADD Email VARCHAR(50);

The following statement changes the name of the CustomerID column to CustomerNumber:
ALTER TABLE Customers
RENAME COLUMN CustomerID TO CustomerNumber;

The following statement adds a new constraint to the Customers table that ensures that the CustomerName column is not null:
ALTER TABLE Customers
ADD CONSTRAINT UQ_CustomerName
UNIQUE (CustomerName);

The following statement drops the CustomerID column from the Customers table:
ALTER TABLE Customers
DROP COLUMN CustomerID;

The ALTER TABLE statement can also be used to change the data type of an existing column. For example, the following statement changes the data type of the CustomerName column from VARCHAR(50) to NVARCHAR(50):
ALTER TABLE Customers
MODIFY COLUMN CustomerName NVARCHAR(50);

The ALTER TABLE statement is a powerful tool that can be used to modify the structure of an existing table. It can be used to add, delete, or modify columns, constraints, indexes, and other table properties.