Altering Tables
The ALTER TABLE statement is used to modify the structure of an existing SQL Server table. We can add, modify or remove columns without recreating the complete table.
What is ALTER TABLE?
The ALTER TABLE statement is used when we need to change the structure of an existing table.
For example, we can add a new column, remove an existing column or change the data type of a column.
ALTER TABLE TableName
ADD ColumnName DataType;
Common ALTER TABLE Operations
| Operation | Purpose |
|---|---|
| Add Column | Adds a new column to an existing table. |
| Alter Column | Changes the data type or definition of a column. |
| Drop Column | Removes a column from the table. |
| Add Constraint | Adds a constraint to the table. |
1. Add a New Column
The ADD clause is used to add a new column to an existing table.
ALTER TABLE Students
ADD Email VARCHAR(150);
The above statement adds an Email column to the Students table.
2. Add Multiple Columns
Multiple columns can be added to a table using ALTER TABLE.
ALTER TABLE Students
ADD Phone VARCHAR(15),
Address VARCHAR(200);
3. Modify a Column
The ALTER COLUMN clause can be used to change the definition or data type of an existing column.
ALTER TABLE Students
ALTER COLUMN StudentName VARCHAR(200);
Here, the maximum length of the StudentName column is changed to 200 characters.
4. Remove a Column
The DROP COLUMN clause is used to remove an existing column from a table.
ALTER TABLE Students
DROP COLUMN Address;
Important
Dropping a column removes that column and its stored data from the table. Use this operation carefully.
5. Add a Constraint
ALTER TABLE can also be used to add constraints to an existing table.
ALTER TABLE Students
ADD CONSTRAINT PK_Students
PRIMARY KEY (StudentId);
Table Before ALTER
| StudentId | StudentName | Age | Course |
|---|---|---|---|
| 1 | Samadhan | 22 | Java |
| 2 | Samiksh | 21 | .NET |
Practical Example
Suppose we want to add an Email column to the Students table.
ALTER TABLE Students
ADD Email VARCHAR(150);
Table After ALTER
| StudentId | StudentName | Age | Course | |
|---|---|---|---|---|
| 1 | Samadhan | 22 | Java | samadhan@example.com |
| 2 | Samiksh | 21 | .NET | samiksha@example.com |
ALTER TABLE Flow
Existing Table
↓
ALTER TABLE
↓
Choose Operation
↓
ADD / ALTER / DROP
↓
Updated Table Structure
Verify the Updated Table
After altering a table, we can verify its structure using the following query:
EXEC sp_help 'Students';
Important Points
- ALTER TABLE changes the structure of an existing table.
- ADD is used to add columns.
- ALTER COLUMN is used to modify a column definition.
- DROP COLUMN removes a column and its data.
- Always be careful when changing a table containing important production data.
Summary
In this section, we learned how to modify an existing SQL Server table using ALTER TABLE. We learned how to add columns, modify columns, remove columns and add constraints.