HomeProgramSQL Server

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

4.3 ★ | 3 Vote
ALTER TABLEMS SQL ServerSQL Servermssql

You should read it

May be interested

  • The difference between Truncate and Delete in Microsoft SQL ServerThe difference between Truncate and Delete in Microsoft SQL Server
    in the following article, we will help you distinguish some basic differences between two delete syntax and truncate table in microsoft sql server application. basically, both of these statements help us to remove the data, but in essence it is not so.
  • EXECUTE AS statement in SQL Server 2005EXECUTE AS statement in SQL Server 2005
    in this article, we will explain the effect of the new execute as statement in sql server 2005 - execute as. this is a very useful utility for database administrators sql server 2005 when they need to check the permissions of each user.
  • CASE statement in SQL ServerCASE statement in SQL Server
    this article will show you in detail how to use the case statement handling function in sql server with specific syntax and examples to better visualize and capture functions.
  • VIEW in SQL ServerVIEW in SQL Server
    the article explains how to create, update and delete view in sql server with syntax and examples.
  • Segment tables in SQL ServerSegment tables in SQL Server
    table partitioning technique (table partitioning) to effectively manage the database with large capacity.
  • GLOBAL TEMPORARY TABLE in SQL ServerGLOBAL TEMPORARY TABLE in SQL Server
    the global temporary table global temporary table in sql server (transact-sql) are tables created separately in sql server sessions,
  • TOP command in SQLTOP command in SQL
    in sql, the top statement is used to retrieve n records or x percent records from a table.
  • USER_NAME function in SQL ServerUSER_NAME function in SQL Server
    this article will show you in detail how to use the function to handle the user_name statement in sql server with specific syntax and examples to better visualize and capture the function.
  • CREATE TABLE command in SQL to create a database tableCREATE TABLE command in SQL to create a database table
    what does the create table command in sql do? in this article, let's learn everything you need to know about the create table statement in sql!
  • DROP TABLE command in SQL ServerDROP TABLE command in SQL Server
    in sql server, the drop table command is used to delete a table from the database.

SQL Server