alter column size take very long time? I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly.

alter table T add C char(25)


Then I found the size is too small so I run the following query.

alter table T alter column C varchar(2500)


And it runs one hour and is still running. sp_whoisactive shows (at the moment, still running)

reads: 48,000,000
writes: 5,000,000
physical reads: 3,900,000


Shouldn't it be really fast?
Assuming all previously-created columns are fixed-width like char and datetime, adding column C above just added it to the end of the fixed-width section of the record (effectively a meta-only change). However, recasting it as a varchar required it to be moved to the variable-width section of the record, forcing an implicit rebuild of the table. Paul Randal explains the internals of records in great detail in Inside the Storage Engine: Anatomy of a Data Record.