MS SQL Server 2012 - DBA Articles

ALTER TABLE - Add, Modify, Delete Column Information

ALTER TABLE is used to modify Structure of Table in the database files. ALTER TABLE is used to add, modify or delete attributes of columns, enable and disable constraints and triggers, IDENTITY, ROWGUIDCOL and move partitioned tables.

ADD keyword is used to define add one or more columns definitions, computed column definitions or table level constraints in the Table. DROP keyword is used to delete one column or constraint from the Table.

ALTER TABLE cannot use to modify timestamp data type, computed column and ROWGUIDCOL for the table.

Used in an index except the column is a varchar, nvarchar or varbinary data type. The data type of text or ntext columns can be changed into varchar(max), nvarchar(max) or xml data type. The image data type can be changed into varbinary(max) data type.

Used in an Index, CHECK, FOREIGN KEY, UNIQUE or PRIMARY KEY constraint cannot be dropped from Table. Associated with a default that is defined with the DEFAULT keyword, or bound to a default object.

Example 01

Add New Column

The following example is used to add new (Email) column in the EMP table.

-- Add New Column --

USE eBusiness

ALTER TABLE EMP

ADD Email char(50)

Example 02

Modify Column Data Type

The following example is used to modify (Email) column data type and size in the EMP table.

-- Modify Column Data Type --

USE eBusiness

ALTER TABLE EMP

ALTER COLUMN Email varchar(108)

Example 03

Delete Existing Column

The following example is used to delete (Email) column from EMP table.

-- Delete Existing Column --

USE eBusiness

ALTER TABLE EMP

DROP COLUMN Email

* * * * *

Your Comment To AUTHOR