alter column size take very long time?
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.
I tested the case. You can do it faster using below steps:
- Create the same table structure with a different name (call it Tbl2)
- Alter the column on Tbl2
- insert data from Tbl1 into Tbl2
- Drop Tbl1 (the old table)
- Rename Tbl2 (the new one) to Tbl1
This will give you much better performance.
The reason is, altering the column on table containing data, will take a lot of data transfer and data page alignment. Using my solution you just insert data w/o any page reorganization.
Edit:
In my test, I used table with about 40 M rows, Table Size 7 GB and Index size 2.5 GB. The suggested method, was 1 minute VS 4 minutes when I used renaming the field in original table