ALTER COLUMN — Change a Data Type

A new overseas signing has a long name that doesn't fit in VARCHAR(50). You need the name column to hold up to 100 characters — same column, bigger type. In SQL Server that is ALTER COLUMN.

Definition

ALTER COLUMN is the part of ALTER TABLE that changes the definition of an existing column — its data type, its size, or whether it allows NULL — while keeping the column's name, position and (when they can be converted) its existing values.

• Data type / size — e.g. VARCHAR(50) → VARCHAR(100), or INT → BIGINT.
• NULL / NOT NULL — e.g. make an optional column required.
• Keeps values — SQL Server converts each existing value to the new type. If any value cannot be converted, the whole statement fails and nothing changes.

Syntax (SQL Server)

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

Example — VARCHAR(50) → VARCHAR(100)

Before:

ALTER TABLE players
ALTER COLUMN name VARCHAR(100) NOT NULL;

After — same column, bigger type:

Step by step: 1️⃣ ALTER TABLE players → 2️⃣ ALTER COLUMN name with the new type. The rows (Virat Kohli, Rohit Sharma…) are untouched.

Note: In the course slide the statement is ALTER TABLE players ALTER COLUMN name VARCHAR(100). That works, but because name was NOT NULL, it's best to repeat NOT NULL — otherwise SQL Server silently changes the column to allow NULL.

Widening vs narrowing

Check your data before narrowing a column:

SELECT MAX(LEN(name)) AS longest_name
FROM players;

The longest name ("Jasprit Bumrah") has 14 characters, so even VARCHAR(20) would be safe.

Syntax differs by database

Renaming a column (bonus)

Renaming is a different operation from changing a type:

-- SQL Server
EXEC sp_rename 'players.name', 'player_name', 'COLUMN';

-- MySQL 8+ / PostgreSQL
ALTER TABLE players RENAME COLUMN name TO player_name;

Common mistakes

• ❌ Forgetting NOT NULL when altering a required column. ✅ Restate it: ALTER COLUMN name VARCHAR(100) NOT NULL.
• ❌ Using MySQL's MODIFY in SQL Server (or vice versa). ✅ Use the syntax of your database.
• ❌ Narrowing a column without checking the data. ✅ Check MAX(LEN(col)) first.
• ❌ Changing a column that is part of a PRIMARY KEY, index or FOREIGN KEY without dropping those first → SQL Server error. ✅ Drop the dependent constraint/index, alter, then recreate it.

Interview tip

Q: How do you change a column's data type in SQL Server, MySQL and PostgreSQL? A: SQL Server — ALTER TABLE t ALTER COLUMN c new_type; MySQL — ALTER TABLE t MODIFY COLUMN c new_type; PostgreSQL — ALTER TABLE t ALTER COLUMN c TYPE new_type.