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:
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