Alter Statements in SQL Server with Examples

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'