SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL ALTER TABLE

Back to: SQL Server Tutorial

The ALTER TABLE statement in Microsoft SQL Server is used to make changes to an existing table structure. This can include adding or deleting columns, changing data types, renaming columns, and more. Here’s a breakdown of how to use ALTER TABLE for various operations:

Adding a Column

To add a new column to an existing table:

ALTER INDEX index_name ON table_name DISABLE;

Example:

ALTER TABLE Employees ADD DateOfBirth DATE;

Dropping a Column

To remove a column from a table:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE Employees DROP COLUMN DateOfBirth;

Modifying a Column

To change the data type of an existing column or modify its properties:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type [NULL | NOT NULL];

Example:

ALTER TABLE Employees ALTER COLUMN LastName NVARCHAR(100) NOT NULL;

Renaming a Column

To rename an existing column, you need to use sp_rename:

EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Example:

EXEC sp_rename 'Employees.LastName', 'Surname', 'COLUMN';

Adding a Constraint

To add constraints like primary keys, foreign keys, or unique constraints:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

Example (adding a primary key):

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

Dropping a Constraint

To remove a constraint from a table:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example (dropping a primary key):

ALTER TABLE Employees DROP CONSTRAINT PK_Employees;

Renaming a Table

To rename a table, you use sp_rename:

EXEC sp_rename 'old_table_name', 'new_table_name';

Example:

EXEC sp_rename 'Employees', 'Staff';

Modifying Table Options

You can also modify various table options such as enabling/disabling indexes, changing schema, etc. This often involves additional SQL commands or system stored procedures.

Example (disabling an index):

ALTER INDEX index_name ON table_name DISABLE;

Always make sure to carefully plan and test changes to table structures, especially in production environments, as these operations can have significant impacts on your database and applications.

Scroll to Top