Alter Table Statement in SQL Server

Introduction

Sometimes their is need to update the existing table  in sql server database such as to add, remove column of existing  table or change the  size or type of data type of table Column. I have explained Alter Statement of Sql serevr with syntax and examples to easy understand. let us I will start it with Basics.

What is Alter Statement in Sql Server ?

The ALTER TABLE statement allows user to modify an existing table such as to add, modify or drop a column from an existing table.
  
Syntax

Alter table table_name ...(list of statements to alter)

In the above Syntax Alter is the command or sql statement ,table is the keyword and  table_name is the name of the existing table in sql server database which you wish to make modification.

Example

Suppose the table_name is emp then query will be:
 

Alter table emp  (list of statements to alter) 

let us see the examples

First create the table in sql server database  named  employee  as 

CREATE TABLE employee

( 

id int not null,

Name varchar (50) ,

city varchar(50) ,

department  varchar (10), 

CONSTRAINT employees_pk PRIMARY KEY (id)

); 

 
Then run a query select *from employee the output should be like this.

Alter-Table-In-SQL-Server.jpg

Adding column's to a table

To add a single  column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name

ADD column_name column-definition;

Example

ALTER TABLE employee

ADD salary  varchar(50);

This will add a column  salary to the existing employee table. To add multiple columns to an existing table, the ALTER TABLE syntax is:

alter table table_name

add (column_1 column-defination,

column_2 column-defination , column_n column-defination)

Example

alter table employee add (region varchar (50),email Varchar (50))

This will add the two columns region and email to the existing employee table.

Modifying Column or Columns of  a table

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name

MODIFY column_name column_type;

Example

ALTER TABLE employee

MODIFY salary  varchar(100) ;

The above query will modify the size of salary column to 100,also you can chnage the datatype name according to your requirement.

Drop column of a existing table

you can also drop the columns of a existing table,the syntax is..

ALTER TABLE table_name

DROP COLUMN column_name;

example

ALTER TABLE employee

DROP COLUMN salary;

The above query will drop the column salary from existing employee table

Reference

http://tempuri.org/tempuri.html