Migrate Using Update Statement

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.

Note: If you have a very large table (for example, over a million rows), executing an update statement may take a very long time. If you use a where clause to select fewer rows at a time, make sure you use a key index to identify all the rows in the table to ensure that you do not miss any rows during the conversion.