Thứ Ba, Tháng Hai 11, 2025
spot_img
HomeALTER TABLE command in SQL Server

ALTER TABLE command in SQL Server

Add columns in SQL Server by what command? How to rename columns in SQL Server how? Let's find out with Quantrimang.com!

If you want to develop in the programming industry, you need to understand the most basic and popular languages ​​today. Besides, tools related to data management also need to be known. Prominent among them is SQL Server.

SQL Server was developed by Microsoft and is extremely famous in the programming world. It is a great database management system, providing extremely useful features such as:

  • Insert, update, delete rows in a data relation.
  • Freely edit objects in the database.
  • Database access control.
  • Ensure consistency and relationship constraints of the database.

Learning SQL Server is not difficult. Basically, you need to understand commonly used commands when using it. Quantrimang.com will summarize for you the knowledge you need to know about that command. And in this article is the Alter SQL Server command.

If you know English, you've probably guessed how to use this command. However, that's not all. Let's learn details with Quantrimang.com about how to use Alter in SQL Server!

ALTER command in SQL Server used to make edits in an existing table. The changes that can be made are adding a column, deleting a column, resizing, editing data types, adding or removing indexes, adding or removing constraints in a table definition. It also allows users to rename or rebuild partitions, disable or enable limits and more. Here's what you need to know about the ALTER command in SQL Server.

The instructions are performed on Microsoft SQL System Sever Management Studio 18 and SQL Server 19.

For the examples in this article we will use the same data table Quantrimang later, created with the Create table command. To view Quantrimang table in MS SSMS you can use query Select * from Quantrimangreplace Quantrimang with the name of the table you need to see.

Below is the table Quantrimang:

 -+-------------+--------------+-------------+-------+
 | IDChuyenmuc | Chuyenmuccon |Chuyenmuclon | Sobai |
 +-------------+--------------+-------------+-------+
 |      1      | SQL Server   |  Lap trinh  |  101  |
 |      2      | Facebook     |  Mang xa hoi|  152  |
 |      3      | Python       |  Lap trinh  |  101  |
 |      4      | JavaScript   |  Lap trinh  |  122  |
 |      5      | Chrome       |  Web        |  94   |
 |      6      | Instagram    |  Mang xa hoi|  165  |
 +-------------+--------------+-------------+-------+

Add columns to a table in SQL Server

Syntax

ALTER TABLE ten_bang
  ADD ten_cot dinh_nghia_cot;

For example

ALTER TABLE Quantrimang
  ADD Luotxem FLOAT(10);

The ALTER TABLE command above will add columns Luotxem into the table Quantrimang.

After running the above command, this is my result:

Add multiple columns to a table in SQL Server

Syntax

ALTER TABLE ten_bang
  ADD cot1 dinh_nghia_cot,
      cot2 dinh_nghia_cot,
      …
      cot_n dinh_nghia_cot;

For example

ALTER TABLE Quantrimang
  ADD Bientap VARCHAR(50),
      Trangthai VARCHAR(50);

The ALTER TABLE command in this example will add 2 columns: Bientap with field VARCHAR(50) and Trangthai with field VARCHAR(50) into the table Quantrimang.

We have the following results:

Add multiple columns at once to the table

Edit table columns in SQL Server

Syntax

ALTER TABLE ten_bang
  ALTER COLUMN ten_cot kieu_cot;

For example

ALTER TABLE Quantrimang
  ALTER COLUMN Trangthai VARCHAR(75) NOT NULL;

The above command will fix the column Trangthai to data type VARCHAR(75) and does not accept NULL values.

Delete a table column in SQL Server

Syntax

ALTER TABLE ten_bang
  DROP COLUMN ten_cot;

For example

ALTER TABLE Quantrimang
  DROP COLUMN Bientap;

The ALTER TABLE command above will delete the column Bientap off the board Quantrimang.

Rename table columns in SQL Server

You can use the ALTER TABLE command to rename columns in a table. Sp_rename can be used, but Microsoft recommends deleting and recreating the table so that scripts and stored procedures do not break.

Syntax

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

For example

sp_rename 'Quantrimang.Chuyenmuccon', 'Muccon','COLUMN';

This example uses sp_rename to rename columns Chuyenmuccon in the table Quantrimang wall Muccon.

Rename table in SQL Server

The ALTER TABLE statement cannot be used to rename a table in SQL Server. Although you can use sp_rename, Microsoft recommends deleting and recreating the table so that scripts and stored processes do not break.

Syntax

sp_rename ‘ten_bang_cu’, ‘ten_bang_moi’;

For example

sp_rename 'Quantrimang','Quantrimang.com';

This command renames the table Quantrimang wall Quantrimang.com.

Add index

You can add an index to an existing column of a table using the ADD INDEX command along with the SQL ALTER command.

ALTER TABLE table_name ADD INDEX index_name [index_type] (key_part,...) [index_option] .

For example:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Name     | varchar(255) | YES  |     | NULL    |       |
| Salary   | int          | NO   |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
| Address  | varchar(50)  | YES  |     | NULL    |       |
| Phone    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.15 sec)

The following query adds indexes to the Name and Salary columns:

ALTER TABLE Employee ADD INDEX sample_index (Name, Salary);

Result:

The results will be displayed as follows:

(0 rows affected)

Verify:

You can verify the table indices using the command EXEC sp_helpindex as below. It is equivalent to the SHOW INDEX command in MySQL.

EXEC sp_helpindex Employee;

Now the structure of the edited indexes in the table will appear as below:

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee | 1          | sample_index | 1            | Name        | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| employee | 1          | sample_index | 2            | Salary      | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.32 sec)

Add primary key

The following is the syntax to add a primary key on an existing database table:

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

For example:

Create a named ID column in the Employee table:

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

Result:

(0 rows affected)

The following query creates a primary key on the ID column:

ALTER TABLE Employee ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(ID);

Result:

(0 rows affected)

Verify:

To verify the above query when you describe the table using the EXEC sp_help command, you can observe the PRI based on the ID field in the Key column:

EXEC sp_help employee;

Result:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| ID       | int          | NO   | PRI | NULL    |       |
| Name     | varchar(255) | YES  | MUL | NULL    |       |
| Salary   | int          | NO   |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
| Address  | varchar(50)  | YES  |     | NULL    |       |
| Phone    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Previous article: PRIMARY KEY in SQL Server

Next article: DROP TABLE command in SQL Server

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments