Alter Table Statement in SQL Server

Sometimes there 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 server 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)
); 
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-definition,
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 change the data type 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

Post a Comment

www.CodeNirvana.in

Protected by Copyscape Online Copyright Protection
Copyright © Compile Mode