How To Use ALTER TABLE in SQL Server

Sometime you want to modify exists table in database. You don't want to delete and recreate. So in SQL Server we can use Alter Table to modify table. We can delete columns, add new columns, modify columns name, modify datatype.

Please follow below scripts:

1. Add Column

Syntax: Alter Table TableName ADD NewField DataType

EX: Alter Table tbl_Staff Add DOB DateTime



2. Delete Column

Syntax: Alter Table TableName Drop Column FieldName

Ex: Alter Table tbl_staff Drop Column DOB

3.Change DataType

Syntax: Alter Table TableName Modify FieldName NewDataType Ex: Alter Table tbl_Staff Modify Address Varchar(30) // Before Address Varchar(50)

4. Change Caption Field

Syntax: Alter Table TableName Change OldField NewField DataType
Ex: Alter Table tbl_Staff Modify Address Addr Varchar(20)

If we know Alter table, we don't spend time to delete and recreate. This is a tip that important for all developer.