MS SQL Server- Alter table
MS SQL Server ALTER Table
- ALTER command is used to make changes in an existing table.
- Modifications can be adding columns, deleting columns, changing data types, adding or removing constraints, and so on.
SQL Server Add New Column
- Syntax to add a new column:
ALTER TABLE table_name ADD column_name data_type column_constraints;
- Syntax to add multiple columns:
ALTER TABLE table_name ADD column_name1 datatype column_constraints, column_name2 datatype column_constraints;
- Example: We are using the table name 'student' given below
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.86 |
| EN19CS301246 | Savi | CSE | 9.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
| EN19CS301258 | Kavi | CSE | 7.50 |
ALTER TABLE student ADD Phone_Number VARCHAR(20) NULL;
| Id | Name | Branch | CGPA | Phone_Number |
| EN19CS301254 | Ravi | CSE | 9.86 | NULL |
| EN19CS301246 | Savi | CSE | 9.23 | NULL |
| EN19CS301248 | Aayush | CSE | 7.25 | NULL |
| EN19CS301258 | Kavi | CSE | 7.50 | NULL |
SQL Server Delete Column
- Syntax to delete a Column:
ALTER TABLE table_name DROP COLUMN column_name;
- Syntax to delete multiple columns:
ALTER TABLE table_name DROP COLUMN column_name1,DROP COLUMN column_name2;
Example:
ALTER TABLE student DROP COLUMN Phone_Number;
- Execute the query to verify whether the column is deleted or not:
EXEC sp_help 'dbo.Student';
| column_name | Type | Computed | Lenght |
| Id | int | no | 4 |
| Name | varchar | no | 65 |
| Branch | char | no | 10 |
| CGPA | int | no | 4 |
Add Constraint on the Column
- Syntax to add a constraint on a column
ALTER TABLE table_name ADD CONSTRAINT[constraint_name]PRIMARY KEY[column_name];
- Constraint names are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.
- Example:
ALTER TABLE Student ADD CONSTRAINT PrimaryKey PRIMARY KEY (Id);
SQL Server Modify Column Data Type
- Syntax to modify column data type:
ALTER TABLE table_name ALTER COLUMN column_name new_data_type(size);
- Example:
ALTER TABLE [Student] ALTER COLUMN Branch VARCHAR(10);
Drop Constraint on the Column
- Syntax to remove constraint:
ALTER TABLE table_name DROP CONSTRAINT [constraint_name];
Example:
ALTER TABLE student DROP CONSTRAINT PrimaryKey;
