.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 15 October 2026
Every ALTER TABLE statement has two costs. There's the one you measure in development, where the table holds 400 rows and the change completes in 30 milliseconds. Then there's the one you discover in production, when the identical statement lands on 90 million rows, takes a schema modification lock, and every query behind it stacks up in a queue that gets longer while you watch. The syntax for all of this fits on one screen. Knowing which variant is metadata-only and which one rewrites the table is the entire job.
Every ALTER TABLE in SQL Server below runs against the three tables this series has been building on, so if you've read the sibling articles the column names will be familiar.
CREATE TABLE dbo.customers (
customer_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
company_name nvarchar(200) NOT NULL,
contact_email varchar(255) NULL,
country_code char(2) NOT NULL,
created_utc datetime2(3) NOT NULL DEFAULT (SYSUTCDATETIME())
);
CREATE TABLE dbo.orders (
order_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
customer_id int NOT NULL REFERENCES dbo.customers (customer_id),
order_date datetime2(3) NOT NULL,
shipped_date datetime2(3) NULL,
status varchar(20) NOT NULL,
total_amount decimal(12,2) NOT NULL
);
CREATE TABLE dbo.order_items (
order_item_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
order_id int NOT NULL REFERENCES dbo.orders (order_id),
sku varchar(40) NOT NULL,
quantity int NOT NULL,
unit_price decimal(12,2) NOT NULL
);Add a column in SQL Server
The cheap case is a nullable column with no default. It's a metadata change, it doesn't touch a single page, and it finishes in milliseconds on a table of any size.
ALTER TABLE dbo.orders
ADD discount_amount decimal(12,2) NULL;Note there's no COLUMN keyword on the way in. ADD column_name, but DROP COLUMN column_name. That asymmetry has caught me more than once. You can add several columns in one statement, comma separated, and all of them go on the end of the table.
Adding a NOT NULL column with a default
This is where edition matters. In Enterprise (and Evaluation, and any Developer edition with the Enterprise feature set), adding a NOT NULL column with a default is an online, metadata-only operation as long as the default is a runtime constant. In Standard and Express, the same statement writes the default value into every existing row.
-- Metadata-only on Enterprise, full table update elsewhere
ALTER TABLE dbo.orders
ADD is_priority bit NOT NULL CONSTRAINT DF_orders_is_priority DEFAULT (0);
-- Full table update on every edition: NEWID() differs per row
ALTER TABLE dbo.orders
ADD tracking_guid uniqueidentifier NOT NULL DEFAULT (NEWID());A runtime constant is one value computed once for the whole statement. Literal 0 qualifies. SYSUTCDATETIME() qualifies. NEWID() does not, because it produces a fresh value per row, so that second statement rewrites the table on Enterprise as well. Max types, xml, the spatial types and anything that would push the row past the 8,060 byte limit also fall off the fast path.
Name the default constraint explicitly, the way the first statement does. Let SQL Server generate one and you get DF__orders__is_prio__3B75D760, a name that comes out different on every environment you deploy to, which makes the constraint impossible to drop from a scripted migration without first running a lookup query against sys.default_constraints, and that lookup is three joins you would rather not have written into a deployment step that runs unattended. Two seconds of typing now.
The hex on the end of a generated name is not random, and people have worked out what it maps to. I went looking once, on the theory that predicting the names would let me skip the lookup. It doesn't help, because the name still comes out different on each environment. I never followed up the rest of it.
ALTER TABLE in SQL Server: ALTER COLUMN and what blocks it
The syntax is short.
ALTER TABLE dbo.customers
ALTER COLUMN contact_email varchar(320) NULL;Instant or full rewrite depends entirely on what you're changing. Widening a variable-length type, varchar(255) to varchar(320), is metadata only. Changing int to bigint physically rewrites every row, because the column's storage width changed. Moving an old datetime column to datetime2(3) rewrites it as well, and SQL Server date functions makes the case for why that migration is worth paying for, mostly because datetime rounds every value it stores onto a three-value millisecond grid and then reports a precision it does not have. Changing a column from NULL to NOT NULL scans the whole table to prove no NULLs exist. Same statement shape, four very different bills.
The longer problem is the list of things that make ALTER COLUMN refuse outright. Microsoft's ALTER TABLE reference spells them out, and it's worth reading the whole list once because the error messages don't always name the real obstacle. A column can't be altered if it's a timestamp, if it's the ROWGUIDCOL, if it's computed or is referenced by a computed column, if it appears in the output of CREATE STATISTICS, if it takes part in a PRIMARY KEY or FOREIGN KEY constraint, if it's in a CHECK or UNIQUE constraint (with an exception for lengthening a variable-length column), or if it has a default definition and you're changing more than length, precision or scale.
On top of that: you can't change the data type of a column in a partitioned table at all, you can't change the type of an indexed column unless it's varchar, nvarchar or varbinary growing to an equal or larger size, and you can't take a primary key column from NOT NULL to NULL.
Working order for a blocked ALTER COLUMN
Every one of those is removable and the order is always the same: take off what's blocking, alter, put it back. User-created statistics get forgotten more than anything else on the list, since auto-created statistics are dropped for you and manual ones are not.
-- 1. Find what's in the way
SELECT i.name AS index_name, i.type_desc
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID('dbo.customers')
AND c.name = 'contact_email';
SELECT s.name AS stats_name, s.auto_created
FROM sys.stats AS s
JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id
JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('dbo.customers')
AND c.name = 'contact_email';
-- 2. Drop, alter, recreate
DROP INDEX IX_customers_contact_email ON dbo.customers;
ALTER TABLE dbo.customers
ALTER COLUMN contact_email varchar(320) NULL;
CREATE NONCLUSTERED INDEX IX_customers_contact_email
ON dbo.customers (contact_email);On Enterprise, SQL Server 2016 and later, WITH (ONLINE = ON) on the ALTER COLUMN lets the rewrite happen while queries keep running. On Standard it isn't available, the table is locked for the duration, and for a type change on a large table that is the whole argument for the copy-and-swap approach at the bottom of this page. I should say plainly that I have used ONLINE = ON on index rebuilds many times and on ALTER COLUMN not once, because every instance I look after day to day runs Standard.
Collation changes and a move from varchar to nvarchar deserve separate thought before you run them, since the comparison rules travel with the column. Going from case-insensitive to case-sensitive can break every existing WHERE clause written against that column without raising an error on a single one of them. What else shifts when a collation does is traced in SQL Server string functions.
Drop a column in SQL Server
ALTER TABLE dbo.orders
DROP COLUMN IF EXISTS legacy_ref;IF EXISTS makes the statement rerunnable, which matters in a migration script that half-applied and got rolled back by hand. The blockers mirror the ALTER COLUMN list: a column can't be dropped while it's part of an index (key column or INCLUDE), while it participates in a CHECK, FOREIGN KEY, UNIQUE or PRIMARY KEY constraint, while it has a default constraint attached or while a rule is bound to it.
The default constraint is the usual culprit, and if it was auto-named you need to look it up first:
SELECT dc.name AS default_constraint
FROM sys.default_constraints AS dc
JOIN sys.columns AS c
ON c.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE dc.parent_object_id = OBJECT_ID('dbo.orders')
AND c.name = 'legacy_ref';
ALTER TABLE dbo.orders DROP CONSTRAINT DF__orders__legacy___4AB81AF0;
ALTER TABLE dbo.orders DROP COLUMN legacy_ref;Microsoft states it plainly: "Dropping a column doesn't reclaim the disk space of the column." The metadata says the column is gone and every page still carries the bytes. A table you dropped four nvarchar(4000) columns from is exactly as large as it was the day before. To get the space back you rebuild:
ALTER TABLE dbo.orders REBUILD; -- rebuilds the table itself
ALTER INDEX ALL ON dbo.orders REBUILD; -- rebuilds every index on itThat rebuild is a size-of-data operation and needs free space in the data file for a second copy of the table, because the old structure isn't released until the rebuild commits. Schedule it. Don't append it to the migration script and hope.
