Alter command in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Alter in SQL Server

When we need to modify the definition of Table then we use alter command. Alter comes under DDL Commands.

To start with alter check the structure of following table:-

Figure 1

To Add New Column in Table

Suppose I want to add new column for dept. in this table then we use alter query in following manner:-

alter table emp

add dept varchar(100)

now run select * from emp

you will get following result:-

Figure 2

To change the data type of existing column

If you want to change the data tayp of any column in that case we use alter as follows:-

alter table emp

alter column dept int

we change the data type of dept from varchar to int

To Delete Existing column

If you want to delete existing column from table then we use alter as follows:-

alter table emp

drop column dept

To Add constraints in column

If you want to add any constraints at any particular column then we can use alter command in as following manner:-

alter table emp

add constraint chk_sal check(salary>0)

in this example I add check contstraints on salary that it can not be zero.

To delete constraints

If you want to delete any constraints at any particular column then we can use alter command in as following manner:-

alter table emp

drop constraint chk_sal