Sql Server info tip of the day: Alter Columns - Knowing what's a "real" change and what's only a "metadata" change

Michael J Swart - Altering Text Columns: Only a Metadata Change?

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.

...

When is the Whole Table Processed?

Here are conditions which require processing the entire table:

...

What’s Changes are Metadata Only Changes?

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

...

What I found interesting was that there were some cases you could change/update a columns data type in-place. I think one of the bigges was the note about text/ntext to varchar(max)/nvarchar(max). Knowing that bit would have helped manage some fears I've had on upgrading those in the past. Learn something new ever day.