Database Tables  «Prev  Next»

Lesson 11 Modifying tables
Objective Modify an existing table in SQL-Server 2025.

Modify a Table with ALTER TABLE in SQL Server 2025

It's not unusual, having created a table, to find that it needs to change afterward: a column to add, a column no longer needed, a data type that turns out to be too narrow, a column to rename, or a constraint that needs to be added, dropped, or temporarily turned off. SQL Server handles all of this with the ALTER TABLE statement, the table-level counterpart to the ALTER DATABASE statement covered earlier in this module.

Basic Syntax

A simplified view of what ALTER TABLE can do:

ALTER TABLE table
[WITH CHECK | WITH NOCHECK]
{ ALTER COLUMN column_name ...
| ADD column_definition
| DROP COLUMN column_name
| ADD CONSTRAINT constraint_name ...
| DROP CONSTRAINT constraint_name
| {CHECK | NOCHECK} CONSTRAINT {ALL | constraint_name [,...n]}
| {ENABLE | DISABLE} TRIGGER {ALL | trigger_name [,...n]}
}
  • table is the name of the table being modified.
  • ALTER COLUMN column_name changes an existing column: a new data type, a new precision or scale, or switching it between NULL and NOT NULL. A column's ROWGUIDCOL property, covered in an earlier lesson, isn't set as part of that same clause; it's added or removed with its own separate form, ADD ROWGUIDCOL or DROP ROWGUIDCOL.
  • ADD column_definition adds a new column, using any of the same column_definition arguments covered in the previous lesson's CREATE TABLE statement.
  • DROP COLUMN column_name removes an existing column, along with its data, entirely.
  • ADD CONSTRAINT constraint_name and DROP CONSTRAINT constraint_name add or remove a constraint, the same kind of PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, or DEFAULT constraint covered when the table was first created.
  • WITH CHECK | WITH NOCHECK, placed immediately before ADD CONSTRAINT or before CHECK CONSTRAINT, controls whether SQL Server validates the table's existing data against a new FOREIGN KEY or CHECK constraint before accepting it. WITH CHECK is the default when adding a brand new constraint; WITH NOCHECK skips that validation, which is faster but leaves a constraint in place that the existing data might actually violate.
  • CHECK CONSTRAINT and NOCHECK CONSTRAINT, by name or with ALL for every constraint on the table, turn an existing FOREIGN KEY or CHECK constraint's enforcement on or off without dropping it. PRIMARY KEY, UNIQUE, and DEFAULT constraints can't be disabled this way; only FOREIGN KEY and CHECK constraints support it.
  • ENABLE TRIGGER and DISABLE TRIGGER, by name or with ALL, turn an existing trigger on or off. A disabled trigger stays defined on the table; it simply stops firing until it's re-enabled.
ALTER TABLE can also reassign a table's partitions or change its data compression settings; those are more advanced operations outside the scope of this lesson.

Adding a Column

Suppose the employee table, built up in the previous lesson, now needs a hire date recorded for each employee, something that wasn't part of the original design. Adding it is a single statement:

ALTER TABLE employee
ADD HireDate date NULL
A newly added column has to allow NULL, unless a DEFAULT is supplied alongside it, since every existing row in the table doesn't have a value for it yet, and SQL Server has to put something in that column for each of those existing rows. Supplying a default instead of leaving the column nullable fills every existing row in automatically:

ALTER TABLE employee
ADD HireDate date NOT NULL DEFAULT '2000-01-01'
Either approach is reasonable depending on whether a genuinely unknown hire date should be recorded as such, or whether every row realistically needs a usable value in that column going forward. More than one column can be added in the same statement, separated by commas, rather than issuing a separate ALTER TABLE statement for each:

ALTER TABLE employee
ADD HireDate date NULL,
    TerminationDate date NULL
This is purely a convenience; running two separate ADD statements back to back accomplishes the same result, just in two round trips to the server instead of one.

Modifying a Column's Data Type

A column's data type or size can turn out to be too narrow after a table's already in use. Widening the Description column on a product table, for instance:

ALTER TABLE Products
ALTER COLUMN Description nvarchar(500) NOT NULL
This kind of change isn't always as cheap as it looks. Widening a variable-length string column like this one is typically a fast, metadata-only operation, but other changes, shrinking a column, changing its fundamental type, or adding NOT NULL to a column that currently allows nulls, can require SQL Server to rewrite every row in the table to verify or convert the existing data, which gets considerably more expensive as a table grows. Adding NOT NULL to an existing column specifically requires every row already in the table to have a non-null value in that column; if even one row doesn't, the statement fails outright, which is worth checking for ahead of time on a table that isn't brand new. Shrinking a column carries its own risk: narrowing Description back down to nvarchar(100) after it's been widened, for instance, fails outright if even one existing row already holds a value longer than the new limit allows. SQL Server won't silently truncate data to make a type change fit; it rejects the statement entirely and leaves the column exactly as it was, which means the existing data is worth checking against the new limit before attempting the change, not after.

Dropping a Column

Removing a column that's no longer needed, a HomeFax column on the employee table that nobody's used in years, say, works the same way:

ALTER TABLE employee
DROP COLUMN HomeFax
This is permanent the moment it runs: the column and every value stored in it for every row are gone, with no step built into the statement to get them back afterward. A column that's referenced by a constraint, an index, or a computed column elsewhere in the table has to have that dependency removed first; SQL Server won't drop a column that something else on the table is still depending on. A DEFAULT constraint on the column being dropped is the one exception worth knowing about: SQL Server drops a column's own default constraint along with the column automatically, without needing it removed as a separate step first. More than one column can be dropped in a single statement the same way more than one can be added:

ALTER TABLE employee
DROP COLUMN HomeFax, AddressLine2
As with dropping a database, taking a backup before dropping a column from a table holding anything worth keeping is worth doing as a matter of habit, for exactly the same reason: there's no statement that puts a dropped column's data back once it's gone.

Adding and Dropping Constraints

A constraint that wasn't part of the original table design can be added afterward the same way a column can. Enforcing that an employee's salary can never go negative, for a table that didn't originally have that check:

ALTER TABLE employee
ADD CONSTRAINT CK_employee_salary CHECK (Salary >= 0)
Removing a constraint uses its name directly:

ALTER TABLE employee
DROP CONSTRAINT CK_employee_salary
This is exactly why naming constraints explicitly, the way the previous lesson recommended, pays off here: dropping or modifying a constraint later requires knowing its name, and a constraint SQL Server named automatically is considerably harder to identify months or years after the table was first created.

Adding a Foreign Key to a Table That Already Has Data

Adding a brand new foreign key constraint to a table that already contains rows raises the same question the WITH CHECK | WITH NOCHECK syntax above was built to answer: should SQL Server confirm every existing row actually satisfies the new relationship before accepting it? Adding a foreign key from the employee table back to the department table, for data that's trusted to already be clean:

ALTER TABLE employee
ADD CONSTRAINT FK_employee_department FOREIGN KEY (DepartmentID)
    REFERENCES Department(DepartmentID)
Written this way, with no WITH NOCHECK prefix, SQL Server defaults to WITH CHECK and checks every existing row against the new constraint before it's accepted; any employee row with a DepartmentID that doesn't exist in the department table causes the whole statement to fail. Prefixing it with ALTER TABLE employee WITH NOCHECK ADD CONSTRAINT ... instead skips that validation and adds the constraint immediately, at the cost of not actually knowing whether the existing data complies until it's checked separately.

Temporarily Disabling a Constraint

Sometimes a constraint needs to stay in place but step out of the way briefly, rather than being dropped and recreated. Loading a large batch of historical records into the employee table, where the data is trusted to already be correct and re-validating every row against the foreign key would simply slow the load down, is a common reason to do this:

ALTER TABLE employee
NOCHECK CONSTRAINT FK_employee_department

-- bulk load happens here

ALTER TABLE employee
WITH CHECK CHECK CONSTRAINT FK_employee_department
The second statement matters as much as the first. Simply disabling a constraint and leaving it that way means SQL Server stops enforcing it, but re-enabling it with the plain CHECK CONSTRAINT form, without the WITH CHECK prefix, brings the constraint back without actually re-validating the data against it; the constraint shows as enabled, but SQL Server hasn't confirmed the data during the time it was off actually satisfies it. Only WITH CHECK CHECK CONSTRAINT both re-enables the constraint and verifies the table's current data against it.

Temporarily Disabling a Trigger

Triggers can be turned off the same way, which is useful when an audit trigger on the employee table would otherwise log every row of that same bulk load as an individual change:

ALTER TABLE employee
DISABLE TRIGGER trg_employee_audit

-- bulk load happens here

ALTER TABLE employee
ENABLE TRIGGER trg_employee_audit
ALL in place of a specific trigger name disables or enables every trigger defined on the table at once, which is convenient but worth using deliberately rather than as a reflex, since it's easy to forget exactly which triggers were running before and re-enable only some of them.

Renaming a Column

ALTER TABLE itself has no clause for renaming a column; that's handled by a separate system stored procedure, sp_rename:

EXEC sp_rename 'employee.HomePhone', 'Phone', 'COLUMN'
The last argument, 'COLUMN', tells sp_rename what kind of object is being renamed, since the same procedure can rename a table, an index, or several other kinds of objects depending on what's passed there. Renaming a column this way updates the column itself, but it doesn't automatically update any view, stored procedure, or application code elsewhere that still refers to the column by its old name; those have to be found and updated separately.

Confirming a Change Took Effect

After altering a table, the same sys.columns catalog view covered in the previous lesson confirms exactly what the table looks like now:

SELECT name, is_nullable, system_type_id
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.employee')
ORDER BY column_id
This is particularly worth running after a column rename or a data type change, where a typo in the statement itself could otherwise go unnoticed until something built on top of the table breaks later.

Permissions Required

Altering a table requires the ALTER permission on that specific table, the same permission the previous lesson's CREATE TABLE statement needed at the schema level, just scoped down to one table instead of everything in the schema. Membership in the db_ddladmin or db_owner fixed database roles, or the sysadmin fixed server role, satisfies this the same way it does for the other permissions covered throughout this module. A login with only enough permission to insert, update, and delete rows in a table, without any of the roles or permissions above, can't change the table's structure; reading and writing data and changing the shape of the table it lives in are two separate permissions, by design.

Operational Considerations

Some ALTER TABLE operations are metadata-only and finish almost instantly, regardless of how large the table is; others rewrite the table's data entirely, which scales with the table's size and can take a meaningful amount of time on a large one. A table being altered this way typically needs a schema modification lock for at least part of the operation, which blocks other queries against that table until the lock is released, including simple reads that would otherwise run instantly. For a large, actively used table, running a potentially expensive ALTER TABLE statement during a planned maintenance window, rather than during normal business hours, is worth doing deliberately rather than finding out the hard way which category a particular change falls into. A quick test against a copy of the table, or simply checking which specific change is being made against the list of metadata-only versus data-rewriting operations in SQL Server's own documentation, costs far less than discovering mid-afternoon that a routine-looking ALTER COLUMN statement has locked a heavily used table for the next twenty minutes.
Between adding, dropping, and modifying columns, adding and dropping constraints, toggling constraints and triggers on and off, and renaming a column through sp_rename, a table built with the basics from the previous lesson can keep evolving to match whatever the data actually turns out to need over its lifetime, without having to be dropped and rebuilt from scratch each time. That's really the point of ALTER TABLE existing as its own statement in the first place: a table's design is rarely perfectly settled on day one, and the alternative, dropping it and starting over every time something needs to change, would mean losing every row already in it along the way. The next lesson covers the simple task of deleting a table entirely.

SEMrush Software 11 SEMrush Banner 11