Migrate Using alter table with Data Copy

An example using alter table, dropping a column to add it back as a new column so that the row content, while essentially unchanged, modifies the description column.

The example shows how you can move LOB columns to in-row storage as a side-effect of an alter table schema change operation that might require a data copy (such as drop column, add not null column, and so on):

1> exec sp_drop_object mymsgs_test_alttab, 'table'
Dropping table mymsgs_test_alttab
(return status = 0)
1> 
2> select * into mymsgs_test_alttab from mymsgs
(9564 rows affected)

1> alter table mymsgs_test_alttab modify description in row (300)
1> 
2> alter table mymsgs_test_alttab
3> DROP dlevel
4> ADD newdlevel int default 0 not null
(9564 rows affected)
1> 
2> exec sp_spaceusage display, 'table', mymsgs_test_alttab
Warning: Some output column values in the result set may be incorrect. Running 'update statistics' may help correct them.
All the page counts in the result set are in the unit 'KB'.
 OwnerName TableName      IndId NumRows UsedPages RsvdPages ExtentUtil ExpRsvdPages
    PctBloatUsedPages PctBloatRsvdPages 
 --------- --------------- ----- ------- --------- --------- ---------- ------------
    ----------------- ----------------- 
dbo       mymsgs_test_alttab   0 9564.0    1252.0    1258.0     99.52        1728.0
               -27.46            -27.20
dbo       mymsgs_test_alttab 255   NULL       6.0      32.0     18.75          16.0
                 0.00            100.00 


(1 row affected)
(return status = 0)
1> 
2> exec sp_spaceusage archive, 'table', mymsgs_test_alttab
Warning: Some output column values in the result set may be incorrect. Running 'update 
statistics' may help correct them.
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'.
(return status = 0)

This is the summary report, showing the space usage information for the tables used in the examples, and the significant decrease in space used. While the size of RsvdPages was 19140KB in the original mymsgs table for the LOB columns (indid=255), this space has decreased by over 95 percent in all three sample tables:

1> exec sp_spaceusage report, 'table', 'mymsgs%', 'OwnerName, TableName, IndId, 
NumRows, RsvdPages, UsedPages, ExtentUtil'
All the page counts in the result set are in the unit 'KB'.
 OwnerName TableName          IndId NumRows RsvdPages UsedPages ExtentUtil 
 --------- ------------------ ----- ------- --------- --------- ---------- 
 dbo       mymsgs                 0  9564.0     318.0     318.0     100.00 
 dbo       mymsgs               255    NULL   19140.0   19132.0      99.95 
 dbo       mymsgs_test_alttab     0  9564.0    1258.0    1252.0      99.52 
 dbo       mymsgs_test_alttab   255    NULL      32.0       6.0      18.75 
 dbo       mymsgs_test_reorg      0  9564.0    1242.0    1230.0      99.03 
 dbo       mymsgs_test_reorg    255    NULL      32.0       6.0      18.75 
 dbo       mymsgs_test_upd        0  9564.0    1258.0    1246.0      99.04 
 dbo       mymsgs_test_upd      255    NULL      32.0       6.0      18.75 

(1 row affected)
(return status = 0)