Metadata-Only Column Changes: Which ALTER COLUMN Is Instant

A metadata-only change finishes in a blink, while a row rewrite touches every row, and both look like one innocent line of ALTER COLUMN. You cannot tell them apart by reading the statement. You can tell them apart by measuring the transaction log.

Two ALTERs that look alike

Picture a Friday night change window. The ticket says “widen one column, one line, five minutes.” Two hours later the log drive is filling and the table is locked. The line was short. The work was not.

The log tells you what an ALTER really did. So let me run six common changes on a table with 100,000 rows. Every change runs inside a transaction that I roll back, so the table ends up exactly as it started. Use a test database.

Build a table with enough rows

A small table hides the difference, so use 100,000 rows. The demo creates one table and drops it at the end.

DROP TABLE IF EXISTS dbo.AlterProbe;

CREATE TABLE dbo.AlterProbe (
    ProbeId int NOT NULL,
    Label varchar(40) NULL,
    Amount int NOT NULL);

INSERT dbo.AlterProbe (ProbeId, Label, Amount)
SELECT value, 'sample', value
FROM GENERATE_SERIES(1, 100000);

Measure the log for six changes

First, a list of the changes. A temporary table keeps the statements and the result together.

DROP TABLE IF EXISTS #Tests;

CREATE TABLE #Tests (TestId int PRIMARY KEY, Change varchar(60), Statement nvarchar(400), LogBytes bigint NULL);

INSERT #Tests (TestId, Change, Statement) VALUES
 (1, 'Widen varchar(40) to varchar(80)', N'ALTER TABLE dbo.AlterProbe ALTER COLUMN Label varchar(80) NULL;'),
 (2, 'Make Label NOT NULL', N'ALTER TABLE dbo.AlterProbe ALTER COLUMN Label varchar(40) NOT NULL;'),
 (3, 'Change int to bigint', N'ALTER TABLE dbo.AlterProbe ALTER COLUMN Amount bigint NOT NULL;'),
 (4, 'Add NOT NULL bit with a default', N'ALTER TABLE dbo.AlterProbe ADD IsReady bit NOT NULL CONSTRAINT DF_AlterProbe_IsReady DEFAULT (0);'),
 (5, 'Add a nullable column', N'ALTER TABLE dbo.AlterProbe ADD Note varchar(50) NULL;'),
 (6, 'Narrow varchar(40) to varchar(20)', N'ALTER TABLE dbo.AlterProbe ALTER COLUMN Label varchar(20) NULL;');

Now the loop. For each change it opens a transaction, runs the ALTER, and reads how many log bytes that transaction has used. Then it rolls back. The transaction starts at zero, so one reading after the ALTER is enough.

DECLARE @id int = 1, @sql nvarchar(400), @used bigint;

WHILE @id <= (SELECT MAX(TestId) FROM #Tests)
BEGIN
    SELECT @sql = Statement FROM #Tests WHERE TestId = @id;

    BEGIN TRANSACTION;
    EXEC (@sql);

    SELECT @used = dt.database_transaction_log_bytes_used
    FROM sys.dm_tran_database_transactions AS dt
    JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id
    WHERE st.session_id = @@SPID AND dt.database_id = DB_ID();

    ROLLBACK TRANSACTION;

    UPDATE #Tests SET LogBytes = @used WHERE TestId = @id;
    SET @id += 1;
END;

SELECT Change, LogBytes, CAST(LogBytes / 100000.0 AS decimal(12,3)) AS bytes_per_row
FROM #Tests
ORDER BY TestId;

Read the numbers

The gap is hard to miss. On my run, widening the varchar logged 804 bytes. Adding a nullable column logged about 1 KB, and adding a NOT NULL bit with a default about 2 KB. Those are metadata-only changes. The bytes per row are close to zero.

The other three logged about 22 MB each, near 228 bytes per row. Making Label NOT NULL, changing int to bigint and narrowing the varchar all rewrote the rows. The surprise for many people is the NOT NULL change. It looks like a rule, but SQL Server treated it as a rewrite here.

Your exact numbers will differ with your version, row size and compression settings. The shape will not. If the log bytes grow with the row count, you are looking at a rewrite. Repeat the test with your own table definition before you pick a window.

Check for NULLs before you tighten a column

Before any NOT NULL change, count the NULLs. A failing ALTER after a long scan is a bad way to find missing data.

SELECT COUNT_BIG(*) AS null_labels
FROM dbo.AlterProbe
WHERE Label IS NULL;

Zero, so the change can proceed. If you find NULLs, repair them first and decide how the application fills the column for new rows.

Even a tiny change takes a lock

Metadata-only does not mean lock-free. Run the widening again and ask which locks the session holds on the table. The DISTINCT folds duplicate lock rows.

BEGIN TRANSACTION;

ALTER TABLE dbo.AlterProbe ALTER COLUMN Label varchar(80) NULL;

SELECT DISTINCT resource_type, request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
  AND resource_database_id = DB_ID()
  AND resource_type = 'OBJECT'
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.AlterProbe');

ROLLBACK TRANSACTION;

The ALTER holds a Sch-M lock, a schema modification lock, on the table until the transaction ends. So schedule it when readers are quiet, and keep the transaction short.

One last check proves the rollbacks worked. The table should still have its three original columns.

SELECT name, max_length
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.AlterProbe')
ORDER BY column_id;

ProbeId, Label with length 40, and Amount with length 4. Nothing stuck. Rollback is part of the test, not a decoration, so watch how long it takes on your own table too. The last block drops the demo objects.

DROP TABLE IF EXISTS #Tests;
DROP TABLE IF EXISTS dbo.AlterProbe;

Measure the log on a copy of your table, then schedule the window around that number.

A short ALTER is not an instant ALTER, it is a request for storage work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.