ALTER Command in SQL SERVER 2012

ALTER: It is used to modify the definition or the structure of the table or definition of the database object.

Syntax:

ALTER TABLE <TABLE_NAME>
[ADD <COLUMN_NAME> DATATYPE[SIZE]]
[ALTER <COLUMN_NAME> DATATYPE[SIZE]]
[DROP COLUMN <COLUMN_NAME>]
[ADD CONSTRAINT <CONSTRAINT_NAME> (COLUMN_NAME)]
[DROP CONSTRAINT <CONSTRAINT>]

1.TO ADD A NEW COLUMN into existing table:

Example:

ALTER TABLE EMP20
ADD TOTALSAL INT

new row is added to the table emp20

Whenever we created a new column it always come at the end of the table, we can't place at beginning or at middle of the table.

SELECT * FROM EMP20

ALTER TABLE EMP20
ADD email_id varchar(15)

new row is added to the table emp20

SELECT * FROM EMP20

Can we add multiple columns to existing table ?

yes, we can by separating comma for column name and data type value pairs as shown below.

ALTER TABLE EMP20
ADD email_id varchar(15),TOTALSAL INT

SELECT * FROM EMP20

2.To modify the data type or size of the existing column
ALTER TABLE EMP20
ALTER COLUMN EMAIL_ID VARCHAR(20)

size of the column capacity got changed with the above query.

ALTER TABLE EMP20
ALTER COLUMN EMAIL_ID INT

the data type of column email_id converted from varchar to int
sp_help emp20

ALTER TABLE EMP20
ALTER COLUMN EMAIL_ID SMALLINT

the data type of column email_id converted from int to smallint
sp_help emp20

3.TO DROP A COLUMN from existing table:
ALTER TABLE EMP20
DROP COLUMN EMAIL_ID

column email_id got dropped

 SELECT * FROM EMP20

ALTER TABLE EMP20
DROP COLUMN TOTALSAL

column TOTALSAL got dropped

SELECT * FROM EMP20

 Can we Drop multiple columns from existing table ?

yes we can as shown below

ALTER TABLE EMP20
DROP COLUMN TOTALSAL,COLUMN EMAIL_ID

SELECT * FROM EMP20

4.How to change the column name of a table ?  
syntax:

SP_RENAME '<TABLE_NAME>.<COLUMN_NAME>','<NEW_COLUMN_NAME>'

SP_RENAME 'EMP20.SAL', 'SALARY'

we will saw how to work with constraints with alter in soon.

Thanks
GSV