Alter

Alter command is used to make the structural changes to a database and its objects. Using Alter, we can change the data type, add/delete columns and the constraints as well. 

Now we will see how to use the Alter command programmatically. 

Let us suppose we have a database EmployeeData

It is having a table EmployeeInfo, and it has 6 columns on it.

ID int NOT NULL,

EmpName VARCHAR(100),

EmpDept VARCHAR(50),

EmpLevel int,

EmpAddress VARCHAR(200),

EmpPhone bigint

While creating EmpDept column having data type VARCHAR(100).

Now we want to change the data type to int. 

Then the query should be as below. 

Syntax:

ALTER TABLE <tbl_Name> ALTER COLUMN <Column_Name> <New_Data_Type>

Example:

ALTER TABLE EmployeeInfo ALTER COLUMN EmpDept int

Now, the data type will be set to int.

In the same way, we can change the data type to VARCHAR, FLOAT etc….

Alter command to add/drop columns to the table.

ADD:

Syntax:

ALTER TABLE <tbl_Name> ADD <Column_Name> <Data_Type>

Example:

ALTER TABLE employeeinfo ADD UserName VARCHAR(50);

ALTER TABLE employeeinfo ADD [Password] VARCHAR(50);

ALTER TABLE employeeinfo ADD [Email] VARCHAR(150);

Drop: To remove a particular object. Here  I want to remove a column from the table.

Syntax:

ALTER TABLE <tbl_Name> DROP COLUMN <Column_Name>

Example:

ALTER TABLE EmployeeInfo DROP COLUMN EmpAddress

In the same way, we can add/remove Constraints (we will discuss it in the upcoming classes), Identity etc…