Methods for Migrating Existing Data

Since the results are all very similar, the migration method you choose depends on your situation and preference.

Each example creates a copy of the original table, mymsgs, using select into. The in-row length of the description column of the copied table is then altered. The method being illustrated is used to migrate the off-row LOB to in-row storage. Space usage is compared between the original table and the modified copy to show that LOB storage decreases substantially.

Set Up the mymsgs Example Table

This example creates the mymsgs table from the pubs2 database, specifying text rather than varchar for the description column in preparation for migrating the column’s contents from off-row to in-row storage.

1> use pubs2
2> go
1> exec sp_drop_object mymsgs, 'table'
2> go
1> create table mymsgs (

          error                int               not null
        , severity             smallint          not null
        , dlevel               smallint          not null
        , description          text
        , langid               smallint          null
        , sqlstate             varchar (5)       null

) lock datarows
2> go
1> insert mymsgs select * from master..sysmessages
2> go
(9564 rows affected)
1> exec sp_spaceusage display, 'table', mymsgs
2> go
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       0    9564.0    372.0     384.0      96.87        320.0
           16.25             20.00
 dbo       mymsgs     255    NULL    19132.0   19140.0      99.95      19136.0
           00                 0.02
1> use pubs2
1> 
2> dump tran pubs2 with no_log
1> 
2> /* Drop the spaceusage stats table before each run */
3> exec sp_drop_object spaceusage_object, 'table'
Dropping table spaceusage_object
(return status = 0)
1> 
2> exec sp_spaceusage archive, 'table', mymsgs
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'.
(return status = 0)

Migrate Using Update Statement

This example uses 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.

Use reorg rebuild

This example uses reorg rebuild to rebuild rows as part of the data movement, rebuilding mymsgs_test_reorg so that it is capable of storing in-row LOBs.

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

1> alter table mymsgs_test_reorg modify description in row (300)
1> 
2> REORG REBUILD mymsgs_test_reorg
Beginning REORG REBUILD of table 'mymsgs_test_reorg'.
(9564 rows affected)
REORG REBUILD of table 'mymsgs_test_reorg' completed.
1> 
2> exec sp_spaceusage display, 'table', mymsgs_test_reorg
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_reorg   0  9564.0    1230.0    1242.0     99.03         272.0
               373.08            356.62
dbo       mymsgs_test_reorg 255    NULL       6.0      32.0     18.75          16.0
                 0.00              0.00 

(1 row affected)
(return status = 0)
1> 
2> exec sp_spaceusage archive, 'table', mymsgs_test_reorg
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'.
(return status = 0)

Migrate Using alter table with Data Copy

This example uses 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)