Add a new column, Drop a column or Change Data Type of the column

Add a new column, Drop a column or Change Data Type of the column

Below you will find examples of adding a new column to a table, dropping a column from a table, changing a data type of the column and more:

Lets create a new table and insert some rows in it.

CREATE TABLE table_1
(
emp_id int,
Last_Name varchar(255),
First_Name varchar(255)
);

Insert into table_1
values (1, 'aa', 'aa')
Insert into table_1
values (2, 'bb', 'bb')
Insert into table_1
values (3, 'cc', 'cc')

Select * from table_1

emp_id	Last_Name	First_Name
1	aa	aa
2	bb	bb
3	cc	cc

Add a new column "salary" of data type 'int' to the table.

ALTER TABLE table_1
ADD salary int

Select * from table_1

emp_id	Last_Name	First_Name	salary
1	aa	aa	NULL
2	bb	bb	NULL
3	cc	cc	NULL

By default the new column is nullable as seen above.

Lets DROP the column "salary" we added above.

ALTER TABLE table_1
DROP COLUMN salary

Select * from table_1

emp_id	Last_Name	First_Name
1	aa	aa
2	bb	bb
3	cc	cc

Now if you try to add a not null column like:

ALTER TABLE table_1
ADD salary int not null

It will show the following error:
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'salary' cannot be added to non-empty table 'table_1' because it does not satisfy these conditions.

Lets add a column with some default values:

ALTER TABLE table_1
ADD salary int not null
CONSTRAINT DF_DefaultValue default (1000)

Select * from table_1

emp_id	Last_Name	First_Name	salary
1	aa	aa	1000
2	bb	bb	1000
3	cc	cc	1000

Above we have added a new column "salary" with a default value of 1000.

Now if you run drop column command (as above):

ALTER TABLE table_1
DROP COLUMN salary

It will give an error like:
ALTER TABLE DROP COLUMN salary failed because one or more objects access this column.

So first we have to drop the constraint and then the column:

Alter Table table_1 drop constraint DF_DefaultValue

ALTER TABLE table_1
DROP COLUMN salary

Select * from table_1

emp_id	Last_Name	First_Name
1	aa	aa
2	bb	bb
3	cc	cc

Again lets add a new column "salary" of type int:

ALTER TABLE table_1
ADD salary int

Select * from table_1

emp_id	Last_Name	First_Name	salary
1	aa	aa	NULL
2	bb	bb	NULL
3	cc	cc	NULL

Data type of the column "salary" is int as you can check with the below query:

Select DATA_TYPE
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'table_1' and COLUMN_NAME = 'salary'

Result:
DATA_TYPE
int

Lets change the data type of column "salary" to Varchar:

ALTER TABLE table_1
ALTER COLUMN salary varchar(20)

Again run the below query and check the data type:

Select DATA_TYPE
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'table_1' and COLUMN_NAME = 'salary'

Result:
DATA_TYPE
varchar