ALTER TABLE statement in SQL Server

In SQL Server, the ALTER TABLE statement is used to 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 example

 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 example

 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 example

 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 example

 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 example

 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.

Syntax

 sp_rename 'ten_bang_cu', 'ten_bang_moi'; 

For example

 sp_rename 'nhanvien', 'nv'; 

This command renames the table to nv.

Previous lesson: Primary key PRIMARY KEY in SQL Server

The following article: DROP TABLE command in SQL Server