SQL ALTER TABLE

The ALTER TABLE statement is used to modify an existing table structure without deleting and recreating it. You can add new columns, remove columns, change the data type of a column, or rename things.

Think of it like editing the column headers on a spreadsheet that already has data in it — you are changing the structure without losing the existing information.

Add a Column

The syntax for adding a column is identical in both databases.

MySQL Query

-- Add a new PhoneNumber column to the Students table
ALTER TABLE Students ADD PhoneNumber VARCHAR(15);

MS SQL Server Query

-- NVARCHAR is preferred in MS SQL Server for Unicode support
ALTER TABLE Students ADD PhoneNumber NVARCHAR(15);

Change (Modify) a Column Definition

This is where MySQL and MS SQL Server use different keywords. MySQL uses MODIFY COLUMN; MS SQL Server uses ALTER COLUMN.

MySQL Query

-- Change the Age column from INT to SMALLINT
ALTER TABLE Students MODIFY COLUMN Age SMALLINT;

-- Change City column size and add NOT NULL
ALTER TABLE Students MODIFY COLUMN City VARCHAR(50) NOT NULL;

MS SQL Server Query

-- MODIFY COLUMN does not exist in MS SQL Server. Use ALTER COLUMN instead.
ALTER TABLE Students ALTER COLUMN Age SMALLINT;

-- Change City column size and add NOT NULL
ALTER TABLE Students ALTER COLUMN City NVARCHAR(50) NOT NULL;

Drop (Remove) a Column

The syntax for dropping a column is identical in both databases.

MySQL Query

ALTER TABLE Students DROP COLUMN PhoneNumber;

MS SQL Server Query

ALTER TABLE Students DROP COLUMN PhoneNumber;

Rename a Column

MySQL Query

-- MySQL supports RENAME COLUMN directly
ALTER TABLE Students RENAME COLUMN City TO CityName;

MS SQL Server Query

-- MS SQL Server uses the sp_rename system procedure to rename a column
EXEC sp_rename 'Students.City', 'CityName', 'COLUMN';

Rename a Table

MySQL Query

-- Rename a table in MySQL
RENAME TABLE Students TO AllStudents;

MS SQL Server Query

-- MS SQL Server uses sp_rename to rename a table
EXEC sp_rename 'Students', 'AllStudents';

Add a Constraint Using ALTER TABLE

The syntax for adding constraints is identical in both databases.

MySQL Query

-- Add a CHECK constraint so Age must be at least 16
ALTER TABLE Students
ADD CONSTRAINT chk_age CHECK (Age >= 16);

-- Add a NOT NULL constraint to an existing column
ALTER TABLE Students MODIFY COLUMN StudentName VARCHAR(50) NOT NULL;

MS SQL Server Query

-- Add a CHECK constraint
ALTER TABLE Students
ADD CONSTRAINT chk_age CHECK (Age >= 16);

-- Add NOT NULL to an existing column using ALTER COLUMN
ALTER TABLE Students ALTER COLUMN StudentName NVARCHAR(50) NOT NULL;

Key Points

• MySQL uses MODIFY COLUMN to change a column's data type or properties. MS SQL Server uses ALTER COLUMN.
• MySQL uses RENAME COLUMN directly. MS SQL Server uses the sp_rename system procedure.
• MySQL uses RENAME TABLE. MS SQL Server uses sp_rename for tables too.
• ADD column, DROP COLUMN, and ADD CONSTRAINT syntax is identical in both databases.
• Always prefer NVARCHAR over VARCHAR in MS SQL Server for Unicode support.