How to Alter Table in SQL Server by using Alter Statement - SQL Server / T-SQL Tutorial Part 35

Scenario:

Let's say that you are working as SQL Server developer in Mortgage Company. In TechBrotherIT database, there is Dbo.Customer Table with below definition.

Create Table dbo.Customer
(FirstName VARCHAR(50),
LastName VARCHAR(50),
Age TinyInt,
PhoneNumber CHAR(9),
DOB Date,
Sex CHAR(1)
)


You got the requirement in which you have to generate Alter statement for below changes


  • Add Column Address to Customer Table
  • Modify column FirstName data type from VARCHAR(50) to VARCHAR(60)
  • Drop Column Age from Customer Table


Solution:

Add Column/Columns to Table:

To add Column to Table, we can use below Alter statement.

Alter Table TableName
Add Column_Name DataType (Size if required)

by using above script, Let's prepare our script to add Address Column to Customer Table.

Alter Table dbo.Customer
Add Address VARCHAR(50)


As address will be variable length character string, I selected VARCHAR data type and max length with 50 characters.

If you need to add more than one column to Table, you can add all of them in single Alter statement. In below statement I added two columns to customer table ColumnName1 and ColumnName2.


Alter Table dbo.Customer
Add ColumnName1 VARCHAR(50),
ColumnName2 Int



Modify Data Type of Existing Column in SQL Server Table:
You can use below Alter statement to modify data type of FirstName column in Customer Table.

Alter Table SchemaName.TableName
Alter Column ColumnName new DataType (Size if required)

Alter statement to modify FirstName data type from VARCHAR(50) to VARCHAR(60)

Alter Table dbo.Customer
Alter Column FirstName VARCHAR(60)

Drop Column in SQL Server Table:
To drop column in existing SQL Server Table, we can use below statement.

Alter table SchemaName.TableName
Drop Column Column_Name

To drop Age column from dbo.Customer table, we can use below statement.

Alter Table dbo.Customer
Drop Column Age

If you need to drop more than one column in Single Alter statement, you can do that as well.

Alter table SchemaName.TableName
Drop Column ColumnName1,ColumnName2,....


Video Demo : How to Alter Table in SQL Server Database by using Alter Statement