Instagram
youtube
Facebook
Twitter

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 university.dbo.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 university.dbo.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;