Michael J. Swart

Say you want to change the type of a text column using the ALTER TABLE … ALTER COLUMN syntax. It is valuable to know how much work SQL Server will have to do to fulfill your request. When your tables are large, it can mean the difference between a maintenance window that lasts five minutes, or one that lasts five hours or more.

I give a list of exactly when you’ll feel that pain and when you won’t.

(BTW, CHAR(MAX) columns are impossible, you’ll get the gag if you figure out why)

When is the Whole Table Processed?

Here are conditions which require processing the entire table:

• switching from unicode to non-unicode or vice versa.
• changing a column from nullable to not nullable.
• going from fixed length field to variable length field or vice versa.
• decreasing the maximum length of a field.
• increasing the maximum length of a fixed length field.
• converting from limited length columns to unlimited or vice versa. (e.g. varchar(10) to varchar(max)).
• collation modifications on non-unicode columns that change character set or code page. (See Collation Hell Part 3 by Dan Guzman)

On large tables, any of the above alterations will be a heavy hitter and will take time and fill transaction log (except that a shrink of fixed length fields seems to only require a scan).

What Changes are Metadata Only Changes?

That’s a lot of conditions! What’s alterations are left?

Not much:

• Increasing the maximum length of a variable length column.
• Changing the type from text to varchar(max).
• Changing the type from ntext to nvarchar(max).
• Any of the above while making a non-nullable field nullable.
• Any of the above with a change in collation (with some big caveats, see Collation Hell Part 3 by Dan Guzman).

These changes are metadata only changes which means SQL Server doesn’t have to touch any of the actual data. So the size of the table will not impact the time it takes SQL Server to process the ALTER TABLE command. But see some notes about concurrency below.

Some Notes

Some notes about the above:

• I ignored and make no claims about migrations where text or ntext is the target column type because the exceptions are strange and that scenario seems to fall under “Why would you want to do that?”
• The above applies to only versions I’ve tested. Specifically 2008, and 2012.
• The metadata-only changes I described above is not entirely on-line. There are still concurrency concerns to watch out for. These ALTER statements still request Schema modification (Sch-M) locks on the table, and once granted, only hold them briefly. But if you try to alter a column on a live environment and some long running query blocks your ALTER TABLE statement, then other queries that need access to the table will be blocked as well. 
• Terms I used
  • fixed length: char(x), nchar(x)
  • variable length: varchar(x), nvarchar(x)
  • unlimited length: varchar(max), nvarchar(max), text, ntext
  • unicode: nchar(x), nvarchar(x), nvarchar(max), ntext
  • non-unicode: char(x), varchar(x), varchar(max), text