The change request is one line. CustomerEmail goes from nvarchar(200) to varchar(320). In a test database with 4,000 rows it runs in under a second, so it went into the release as a single ALTER TABLE and nobody costed it.
Production has 200 million rows, six nonclustered indexes, four foreign keys, a schema-bound view and change tracking turned on for the sync job. The same statement takes a schema modification lock on the table, rewrites every row, logs every one of them, and blocks every reader and writer until it finishes or you kill it. Killing it starts a rollback that takes longer than the operation did.
The gap between those two outcomes is not the statement. It is whether anybody checked what the change touches before running it.
Find out what you are about to touch
Nothing else in this procedure matters until you know the dependency surface. Run this first:
DECLARE @Schema sysname = N'dbo',
@Table sysname = N'OrderHeader',
@Column sysname = N'CustomerEmail';
DECLARE @ObjectId int = OBJECT_ID(QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table));
-- Indexes that key or include the column
SELECT 'index' AS Dependency, i.name, i.type_desc, ic.is_included_column
FROM sys.index_columns AS ic
JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = @ObjectId AND c.name = @Column;
-- Modules, views and constraints that reference it
SELECT 'reference' AS Dependency,
OBJECT_SCHEMA_NAME(d.referencing_id) AS ReferencingSchema,
OBJECT_NAME(d.referencing_id) AS ReferencingObject,
o.type_desc
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_id = @ObjectId
AND (d.referenced_minor_id = 0
OR d.referenced_minor_id = COLUMNPROPERTY(@ObjectId, @Column, 'ColumnId'));
-- The table-level features that rule out an online change
SELECT 'feature' AS Dependency,
t.is_replicated,
t.is_merge_published,
t.is_tracked_by_cdc,
t.temporal_type_desc,
HasChangeTracking = CASE WHEN EXISTS (SELECT 1
FROM sys.change_tracking_tables AS ct
WHERE ct.object_id = t.object_id)
THEN 1 ELSE 0 END
FROM sys.tables AS t
WHERE t.object_id = @ObjectId;
sys.sql_expression_dependencies covers modules and views. Foreign keys live in sys.foreign_keys, and computed columns and check constraints in sys.computed_columns and sys.check_constraints, so add those three by hand. Every item on the combined list either blocks the change, has to be dropped and recreated around it, or turns an online operation into an offline one.
While you are measuring, get the data facts too: row count, MAX(DATALENGTH(CustomerEmail)), and whether any row holds a character the target code page cannot represent. Sizing the target column is a different decision from migrating to it, and it is worth settling first: what each type costs you in bytes per row.
Metadata only, or a full rewrite
This is the question that decides whether you need the rest of the article.
Widening a variable-length column at the same nullability, varchar(50) to varchar(100), updates metadata and stops. It is instant on any table of any size.
Everything else rewrites the table. Narrowing a column, changing nullability, and any change to the on-disk representation all fall on this side: int to bigint, varchar to nvarchar, datetime to datetime2, decimal(9,4) to decimal(19,4). The engine touches every row and logs every one, so the transaction log has to hold the whole operation and any availability group secondary has to redo it.
Estimate the log cost before you start, not after the file fills. The floor is the row width times the row count, and the log cannot truncate past the oldest active transaction while the statement runs.
The ONLINE = ON route
SQL Server 2016 added an online form:
ALTER TABLE dbo.OrderHeader
ALTER COLUMN CustomerEmail varchar(320) NULL
WITH (ONLINE = ON);
Microsoft on what this buys: "Long-term table locks aren't held for the duration of the online ALTER COLUMN operation, which allows queries to run as usual." Your readers and writers keep working.
What stops is DDL: "While the online ALTER COLUMN operation is running, any DDL operation that could depend on that column (such as creating or modifying indexes or views) is blocked, or fails with an appropriate error." Any index maintenance job that fires mid-operation fails.
The restrictions are where most tables fall out. One column at a time. Not supported on a table with change tracking enabled or one that publishes merge replication. Not supported when you narrow precision on a column referenced by a check constraint. WAIT_AT_LOW_PRIORITY cannot be combined with it. Microsoft's ALTER TABLE page lists more, including CLR types, XML schema collection changes, ADD/DROP PERSISTED and system-versioned temporal tables. Read it against the dependency list you just built.
On edition: both "Online index create and rebuild" and "Online schema change" are Enterprise Edition rows in the SQL Server 2022 editions matrix, and Microsoft names online ALTER COLUMN in neither one. Test it on your edition before you plan around it.
The shadow column route
This works on every edition and every version, and it is what large shops run. You control the pace, you can stop between batches, and nothing holds a long lock.
Add the new column nullable, so the add is metadata only:
ALTER TABLE dbo.OrderHeader ADD CustomerEmail_v2 varchar(320) NULL;
Keep it current with a trigger so writes landing during the backfill are not lost:
CREATE OR ALTER TRIGGER dbo.tr_OrderHeader_EmailShadow
ON dbo.OrderHeader
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(CustomerEmail)
UPDATE oh
SET oh.CustomerEmail_v2 = CAST(i.CustomerEmail AS varchar(320))
FROM dbo.OrderHeader AS oh
JOIN inserted AS i ON i.OrderID = oh.OrderID;
END;
UPDATE() returns true for every column on an INSERT, so inserts are covered by the same branch.
Backfill in batches bounded by the clustered key. Drive the loop off a key range rather than TOP, so every batch seeks and the plan holds:
DECLARE @Lo bigint = (SELECT MIN(OrderID) FROM dbo.OrderHeader),
@Hi bigint = (SELECT MAX(OrderID) FROM dbo.OrderHeader),
@Batch int = 4000;
WHILE @Lo <= @Hi
BEGIN
UPDATE dbo.OrderHeader
SET CustomerEmail_v2 = CAST(CustomerEmail AS varchar(320))
WHERE OrderID >= @Lo
AND OrderID < @Lo + @Batch
AND CustomerEmail IS NOT NULL;
SET @Lo += @Batch;
WAITFOR DELAY '00:00:00.100';
END
Lock escalation fires when "a single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index," which is why @Batch is 4,000 and not 20,000. Note that @Batch is a key-range width rather than a row count: on a gap-heavy key each pass takes fewer rows than that, and on a table with several rows per key value it takes more. Count the rows in one batch before you trust the number.
The WAITFOR gives log backups, the availability group send queue and the secondary redo thread room to keep up. Watch log growth and log_send_queue_size in sys.dm_hadr_database_replica_states between batches, and lengthen the delay if either climbs. The same loop shape covers any large row-by-row rewrite, including a purge: the batching pattern that keeps the log and the locks in check.
Verify, then swap. Compare row counts and a checksum across both columns, and build any index the new column needs before the swap rather than after. Then rename inside one transaction, which is the only part of this that takes a schema modification lock, and it holds for milliseconds:
BEGIN TRANSACTION;
EXEC sys.sp_rename 'dbo.OrderHeader.CustomerEmail', 'CustomerEmail_old', 'COLUMN';
EXEC sys.sp_rename 'dbo.OrderHeader.CustomerEmail_v2', 'CustomerEmail', 'COLUMN';
COMMIT TRANSACTION;
Microsoft on sp_rename: "Renaming an object such as a table or column won't automatically rename references to that object. You must modify any objects that reference the renamed object manually." A schema-bound reference will block the rename rather than let it through. Script those module changes from the dependency list and deploy them in the same window.
Drop the trigger, drop the old column, reclaim the space. Dropping a column leaves its bytes in the rows. For a variable-length column DBCC CLEANTABLE reclaims them, and Microsoft offers rebuilding the indexes as the alternative while calling it "a more resource-intensive operation." The command is fully logged, it does not reclaim space from a dropped fixed-length column, and Microsoft warns against running it as routine maintenance. Update statistics afterwards either way, or you paid for the migration and kept the pages.
What to do before the next release
Keep a rollback point at every stage. The shadow column is additive until the rename, so up to that transaction you can abandon the migration by dropping the trigger and the column and losing nothing. After the rename, your rollback is the reverse rename, which is why CustomerEmail_old stays on the table until the application has run against the new column through a full business cycle.
And check the dependency queries against the next schema change before it ships, not after it has been running for six hours.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.