The following examples show the different syntax for each method used to migrate off-row LOB data to in-row storage, as well as compare the results. Since the results are all very similar, which 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.
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)
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.
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.
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)
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)
