Alter Statements in SQL Server with Examples

Posted May 27, 2015 by Vishwanath Dalvi in SQL Server

feature-sql1

Alter Table is a DDL (Data Definition Language) statement which is used to add, drop, or alter existing columns. With this statement, you can also rename a table or column and make constraints. Using a table as an example, this tutorial demonstrates alter table statements with syntax.

You can download the examples used in this article and try them out on your local machine here. You can verify the changes to table structure using sp_help ‘Employee’.

First, we begin by creating a table.

Create Table Employee
(
EmployeeId INT,
EmployeeName VARCHAR(25),
Salary INT,
ContactNo VARCHAR(10),
DeptId INT,
Active BIT,
);

Create Table Department
(
DeptId INT PRIMARY KEY,
DeptName VARCHAR(50)
);

SQL Server – Add a Column to an Existing Table

Syntax

ALTER TABLE [TableName]
ADD   [ColumnName] [DataType] [Null OR Not Null]

Example

ALTER TABLE Employee
ADD   Address VARCHAR(255) NOT NULL

SQL Server – Drop an Existing Column from a Table

Syntax

ALTER TABLE  [TableName]
DROP  COLUMN [ColumnName]

Example

ALTER TABLE Employee
DROP COLUMN ADDRESS

SQL Server – Change data type of an Existing column

Syntax

ALTER TABLE [Tablename]
ALTER COLUMN [ColumnName] [DataType]

Example

ALTER TABLE Employee
ALTER COLUMN Salary DECIMAL(18,2)

SQL Server – Change the Size of an Existing Column

Syntax

ALTER TABLE [TableName]
ALTER COLUMN [Columnname] [DataType]

Example

ALTER TABLE Employee
ALTER COLUMN EmployeeName VARCHAR(50)

SQL Server – Add A Primary Key Constraint to an Existing Table

Syntax

ALTER TABLE [TableName]
ADD   CONSTRAINT [Constraint Name] PRIMARY KEY (ColumnName)

Example

ALTER TABLE Employee
ADD   CONSTRAINT PK_Employee_EmployeeId PRIMARY KEY (EmployeeId)

SQL Server – Drop the Existing Primary Key Constraint on an Existing Table

Syntax
ALTER  TABLE [TableName]
DROP   CONSTRAINT [ConstraintName]

Example

ALTER TABLE Employee
DROP  CONSTRAINT PK_Employee_EmployeeId

SQL Server – Add a Foreign Key Constraint

Syntax

ALTER TABLE [TableName]
ADD CONSTRAINT [Constraintname] FOREIGN KEY (ColumnName) REFERENCES [TableName] (ColumnName)

Example

ALTER TABLE Employee
ADD CONSTRAINT Fk_Employee_DeptId  FOREIGN KEY (EmployeeId) REFERENCES Department(DeptId)

SQL Server – Drop a Foreign Key Constraint

Syntax

ALTER TABLE [TableName]
DROP  CONSTRAINT [Foreign_Key_Constraint_Name]

Example

ALTER TABLE Employee
DROP  CONSTRAINT Fk_Employee_DeptId

SQL Server – Add a Check Constraint to an Existing Table

Syntax

ALTER TABLE [TableName]
ADD   CONSTRAINT [Check_Constraint_Name] CHECK (Check_Condition)

Example

ALTER TABLE Employee
ADD   CONSTRAINT CK_Employee_Salary CHECK (Salary > 0)

SQL Server – Drop Check a Constraint on an Existing Table

Syntax

ALTER TABLE [TableName]
DROP  CONSTRAINT [Check_Constraint_Name]

Example

ALTER TABLE Employee
DROP  CONSTRAINT CK_Employee_Salary

SQL Server – Add a NULL or NOT NULL Constraint to an Existing Table

Syntax

ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] [DataType] [NULL OR NOT NULL]

Example

ALTER TABLE Employee
ALTER COLUMN ContactNo VARCHAR(10) NULL

SQL Server – Add a Default Constraint to an Existing Table

Syntax

ALTER TABLE [TableName]
ADD   CONSTRAINT [DefaultConstraintName]
DEFAULT [DefaultValue] FOR [ColumnName]

Example

ALTER TABLE Employee
ADD   CONSTRAINT DF_Employee_Active
DEFAULT 0 FOR ACTIVE

SQL Server – Drop a Default Constraint on an Existing Table

Syntax

ALTER TABLE [TableName]
DROP  CONSTRAINT [Default_ConstraintName]

Example

ALTER TABLE Employee
DROP  CONSTRAINT DF_Employee_Active

SQL Server – Change the Collation of an Existing Column

Syntax

ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] [DataType] COLLATE [CollationName];

Example

ALTER TABLE Employee
ALTER COLUMN EmployeeName varchar(25) COLLATE Latin1_General_CI_AS NOT NULL;

SQL Server – Disable a Foreign Key Constraint

Syntax

ALTER TABLE [TableName]
NOCHECK CONSTRAINT [ConstraintName];

Example

ALTER TABLE Employee
NOCHECK CONSTRAINT Fk_Employee_DeptId;

SQL Server – Disable a Check Key Constraint

Syntax

ALTER TABLE [TableName]
NOCHECK CONSTRAINT [ConstraintName];

Example

ALTER TABLE Employee
NOCHECK CONSTRAINT CK_Employee_Salary;

SQL Server- Rename a Column in a Table

Syntax

EXEC sp_RENAME TableName.OldColumnName, NewColumnName, ColumnName

Example

EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'
 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.