LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 1117 min read

Altering Tables

Use ALTER TABLE to add, modify, and drop columns on an existing SQL Server table without losing your data.

Introduction

Table designs evolve. A requirement changes, a new field is needed, or a data type turns out to be too small. Rather than dropping and recreating a table — which would destroy any data already in it — SQL Server lets you modify an existing table in place using ALTER TABLE.

ALTER TABLE Syntax

ALTER TABLE always names the target table, followed by exactly one type of change: ADD, ALTER COLUMN, or DROP COLUMN. Each ALTER TABLE statement performs one kind of modification at a time (though you can add multiple new columns in a single ADD).

Adding Columns

Use ALTER TABLE ... ADD to introduce new columns to an existing table. If the table already has rows and the new column is NOT NULL, you must supply a DEFAULT value so existing rows have something to populate it with.

ALTER TABLE Students
ADD PhoneNumber NVARCHAR(20) NULL;
Messages

Click Run to see what this code prints.

-- Adding a NOT NULL column to a table that may already have rows
-- requires a DEFAULT so existing rows have a value:
ALTER TABLE Students
ADD GraduationYear INT NOT NULL DEFAULT 2026;
Messages

Click Run to see what this code prints.

Modifying Columns

ALTER COLUMN changes an existing column's data type, size, or nullability. SQL Server will refuse the change if it would cause data loss — for example, shrinking a VARCHAR(100) column down to VARCHAR(20) when a longer value already exists.

ALTER TABLE Students
ALTER COLUMN PhoneNumber NVARCHAR(30) NULL;
Messages

Click Run to see what this code prints.

Changing NULL to NOT NULL

Switching a column from allowing NULL to NOT NULL with ALTER COLUMN will fail if any existing row currently has NULL in that column. Update or backfill the existing NULLs first, then apply the ALTER COLUMN.

Dropping Columns

DROP COLUMN permanently removes a column and all of its data from the table.

ALTER TABLE Students
DROP COLUMN GraduationYear;
Messages

Click Run to see what this code prints.

Data Loss Warning

DROP COLUMN is irreversible — every value stored in that column across every row is deleted immediately and permanently. There's no undo apart from restoring a backup taken before the change.

Renaming Columns and Tables

Renaming is handled a bit differently in T-SQL: there's no RENAME COLUMN clause on ALTER TABLE. Instead, you use the built-in sp_rename system stored procedure for both tables and columns.

-- Rename a column: sp_rename 'Table.OldColumn', 'NewColumn', 'COLUMN';
EXEC sp_rename 'Students.PhoneNumber', 'ContactNumber', 'COLUMN';


-- Rename a table: sp_rename 'OldTable', 'NewTable';
EXEC sp_rename 'Students', 'Learners';


-- (Renaming back so later lessons can keep using "Students":)
EXEC sp_rename 'Learners', 'Students';
Messages

Click Run to see what this code prints.

Common Mistakes

Avoid These Mistakes
  • Adding a NOT NULL column without a DEFAULT to a table that already has rows, which SQL Server will reject.
  • Trying to shrink a column's size when existing data is already longer than the new limit.
  • Expecting a RENAME COLUMN syntax in ALTER TABLE — SQL Server uses sp_rename instead.
  • Dropping a column that's referenced by a constraint, index, or default without removing that dependency first.

Best Practices

  • Always supply a sensible DEFAULT when adding a NOT NULL column to a table that may already contain rows.
  • Test ALTER TABLE changes on a copy of production data (or Developer Edition locally) before running them against a live system.
  • Back up a table — or the whole database — before running a DROP COLUMN or a data-type-shrinking ALTER COLUMN.
  • Use sp_rename sparingly in production; renaming can silently break views, stored procedures, and application code that reference the old name.

Frequently Asked Questions

Yes. You can list multiple column definitions after ADD, separated by commas, and they'll all be added in one statement.

It depends on the specific change. Adding a nullable column is typically a fast, metadata-only operation, while changing a data type or adding a NOT NULL column with a default can require scanning or rewriting existing rows, and may briefly lock the table on very large tables.

Not automatically. Some changes (like ADD) can be reversed with a matching DROP COLUMN, but changes that discard data, like shrinking a column or dropping one entirely, cannot be undone without a backup.

That message is a standard warning SQL Server always displays when you rename an object, reminding you that other code referencing the old name may break. It does not indicate the rename failed.

Key Takeaways

  • ALTER TABLE ... ADD introduces new columns; NOT NULL additions to populated tables need a DEFAULT.
  • ALTER TABLE ... ALTER COLUMN changes a column's type, size, or nullability, and fails if it would lose data.
  • ALTER TABLE ... DROP COLUMN permanently removes a column and its data.
  • Renaming tables and columns uses the sp_rename system stored procedure, not ALTER TABLE.

Summary

You can now evolve a table's structure safely as requirements change. Sometimes, though, a table needs to disappear entirely — the next lesson covers dropping tables, and the important distinctions between DROP, TRUNCATE, and DELETE.

Next Lesson →

Dropping Tables