SQL Server – ALTER and DROP a Table

In my previous article we have already seen how to create a simple table (Customer). Now we will see how to make different changes to the table, and alter it structure. We shall also see how to drop the table from the database permanently.

We shall use the same Customer table, and make alternation in its structure:

Customer Table

Customer Table

Alter a Table

As a DBA, we always come across tables which need some modifications. Some time table created by us also needs to be altered because of change in the specifications.

To change the structure of the table, ALTER command is used. We can make following changes using ALTER statement:

  • Add a Column

To add a new ‘Sex’ column to the Customer table, execute the following statement:

ALTER TABLE Customer ADD Sex VARCHAR(10);

The Customer table will look like this:

Customer Table

Customer Table

  • Alter Datatype of a Column

To alter the ‘Phone’ columns Datatype from VARCHAR to INT, execute the following statement:

ALTER TABLE Customer ALTER COLUMN Phone INT;

The Customer table will look like this:

Customer Table

Customer Table

  • Drop a column

To delete the ‘Sex’ column from the Customer table, execute the following statement:

ALTER TABLE Customer DROP COLUMN Sex;

The Customer table will look like this:

Customer Table

Customer Table

Drop the Table

Many times we across a situation when have need to delete the table from the database permanently. We shall delete the Customer table by executing the following statement:

DROP TABLE Customer;

The Customer table will be deleted from the database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: