ALTER TABLE Statement in SQL Server

ALTER TABLE Statement in SQL Server is easier to understand when the core ideas are paired with practical examples. The sections below explain the topic clearly, highlight useful steps, and point out details that can prevent common errors.

In SQL Server, the ALTER TABLE statement helps add columns, edit columns, delete columns, rename columns or rename tables.

Add column to table in SQL Server

Syntax

 ALT ER TABLE ten_bang 
  ADD te n_cot dinh_nghia_cot;

For instance,

 ALTER TABLE n hanvien 
  ADD ho VARCHA R (50);

The above ALTER TABLE statement will add the column ho to the table.

Add multiple columns to the table in SQL Server

Syntax

 ALTE R TABLE ten_bang 
  ADD cot1 dinh_nghia_cot, 
  cot2 dinh_nghia_cot, 
  … 
  cot_n dinh _nghia_cot;

For instance,

 ALTER TABLE n hanvien 
  ADD ho VARCHAR(50), 
  ten VARCHAR(40);

The ALTER TABLE statement in this example will add two columns that are ho with the VARCHAR (50) field and ten with the VARCHAR (40) field in the table.

Edit the column in the table in SQL Server

Syntax

 ALTE R TABLE ten_bang 
  ALTER COLUMN ten_cot kieu_cot;

For instance,

 AL TER TABLE nhanvien 
  ALTE R COLUMN ho VARCHAR (75) NOT NULL;

The above command will modify the ho column to VARCHAR data type (75) and NULL value will not be accepted.

Delete the column of the table in SQL Server

Syntax

 ALTE R TABLE ten_cot 
  DROP C OLUMN ten_cot;

For instance,

 ALTER TABLE home 
  DROP COLUMN ho ;

The above ALTER TABLE statement will delete the column ho from the table.

Rename the column of the table in SQL Server

You can use the ALTER TABLE command to rename the column in the table. Sp_rename can be used, but Microsoft encourages deleting and recreating the table so that the script and storage processes are not corrupted.

Syntax

 sp_rename 'ten_bang.ten_cot_cu', 'ten_cot_moi', 'COLUMN';

For instance,

 sp_rename 'nhanvien.ho', 'honhanvien', 'COLUMN';

This example uses sp_rename which will change the column name in the table to become a member.

Rename the table in SQL Server

Cannot use the ALTER TABLE command to rename a table in SQL Server. However, you can use sp_rename, but Microsoft recommends deleting and recreating the table so that the script and the storage process are not corrupted.