An example using update set column = column to migrate off-row LOBs to in-row storage.
The select into command first creates a copy of mymsgs, including its off-row data, into mymsgs_test_upd, moving the off-row data in-row in the process. You can then move the update command to relocate off-row LOBs to in-row storage:
1> exec sp_drop_object mymsgs_test_upd, 'table' Dropping table mymsgs_test_upd (return status = 0) 1> 2> select * into mymsgs_test_upd from mymsgs (9564 rows affected) 1> 2> exec sp_spaceusage display, 'table', mymsgs_test_upd 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_upd 0 9564.0 318.0 320.0 99.37 272.0 22.31 17.65 dbo mymsgs_test_upd 255 NULL 19132.0 19136.0 99.97 19136.0 0.00 0.00 (1 row affected) (return status = 0)The space usage of the mymsgs_test_upd is nearly the same as that of the mymsgs table. The off-row LOB consumes about 19KB of storage.
) 1> alter table mymsgs_test_upd modify description in row (300) 1> sp_spaceusage 2> go 2> update mymsgs_test_upd set description = description (9564 rows affected) 1> 2> exec sp_spaceusage display, 'table', mymsgs_test_upd 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_upd 0 9564.0 1246.0 1258.0 99.04 272.0 379.23 362.50 dbo mymsgs_test_upd 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_upd Data was successfully archived into table 'pubs2.dbo.spaceusage_object'. (return status = 0)
The size of RsvdPages for the data layer, indid=0, has changed; what used to be 320KB, is now 1258KB, while the reserved pages for the LOB column, indid=255, has decreased from 19136KB to around 32KB, showing that the off-row storage changed to in-row.