How to use the Alter Statement to Modify a Table Structure in SQL Server

How to Modify the Table Structure using Alter Statement in SQL Server
Alter is DDL command is used to modify the structure of the schema after the creation.
Scenario :

Suppose we have an Employee table with 3 Columns Emp_Id, Emp_Name, Emp_Salary. Now let's use the alter commands to modify the Employee table structure.

By using Alter command we can do the following operations in SQL Server:

Add Column - Adds a column to a table
Add Primary Key - Adds a primary key to a table
Add Foreign Key - Adds a foreign key to a table
Add Constraint - Adds a check constraint to a table
Change Column Type - Changes the data type of a column
Drop Column - Drops a column from a table
Drop Constraint - Drops a check constraint from a table
Drop Primary Key - Drops a primary key from a table
Drop Foreign Key - Drops a foreign key from a table
Rename Table - Renames a table
Rename Column - Renames a column

Examples :

Adding a Primary Key Constraint on an existing Column :

ALTER TABLE Employee  ADD PRIMARY KEY(Emp_id) 

or

ALTER TABLE Employee  ADD CONSTRAINT Emp_id_pk PRIMARY KEY(Emp_id) 

Droping a Constrint :

ALTER TABLE Employee DROP CONSTRAINT Emp_id_pk;

Adding a Foreign Key Constraint on an existing Column :

ALTER TABLE Orders  ADD FOREIGN KEY (Emp_id) References Employee(Emp_id)

or

ALTER TABLE Orders  ADD CONSTRAINT Emp_id_fk FOREIGN KEY (Emp_id) References Employee(Emp_id)

Increasing or Decreasing the size of an existing column :

ALTER TABLE Employee  ALTER COLUMN Emp_name varchar(100) 

Changing the Data Type of an existing Column :

ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) 

Adding  a NULL Constraint on Column :

ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) NULL 

Adding a NOT NULL Constraint on an existing Column :

ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) NOT NULL 

Adding a new Column on Table without any Constraint :

ALTER TABLE Employee  ADD Emp_age varchar(10) 

Add a new Column on Table with a Constraint :

ALTER TABLE Employee  ADD Emp_code varchar(100) CONSTRAINT Emp_code_uq UNIQUE 

Droping a Column from Table :

ALTER TABLE Employee DROP COLUMN Emp_code

 

Adding a Check Constraint Column :

ALTER TABLE Employee ADD CONSTRAINT Emp_age_ck CHECK(Emp_age > 18) 

Thanks, Tamatam