ALTER TABLE Statements in SQL Server

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and remove various constraints from an existing table.

Examples:

To add a column in an existing table.
ALTER TABLE dbo.tbStudents
ADD HireDate DATETIME(20) NULL, iUpdatedUser INT NULL ;

To change the data type of the column
ALTER TABLE tbStudents
ALTER COLUMN iUpdatedUser BIGINT;

To delete the column from an existing table.
ALTER TABLE dbo.tbStudents
DROP COLUMN iUpdatedUser;

To add a column in an existing table along with integrity constraint.
ALTER TABLE tbStudents
ADD bStatus BIT Default (0)

ALTER TABLE tbStudents
ADD EmergencyContact INT NOT NULL;

To add constraints to existing columns in an existing table.
ALTER TABLE tbStudents
ADD FOREIGN KEY (GroupID) REFERENCES Group(GroupId);

ALTER TABLE tbStudents
ADD PRIMARY KEY(iStudentID,iBatchID);

ALTER TABLE dbo.tbStudents
ADD CONSTRAINT PK_iStudentID PRIMARY KEY NONCLUSTERED (iStudentID);

To drop the constraint from a table.
ALTER TABLE dbo.tbStudents
DROP CONSTRAINT PK_iStudentID;

Hope you find this article helpful.

For the complete SQL Server tutorial, please see the index page.