Some data definition language (DDL) and utility operations that work on an entire table copy all table data by rewriting the data row to match that of the target schema. There are several ways to convert LOBs to use in-row storage:
update set column = column
alter table schema changes such as add not null, modify datatype or nullability, or drop column – row contents and layout are reorganized to accommodate the schema change. The entire row is rebuilt.
alter table ... partition by changes the partitioning schema by distributing the data rows to different partitions. Rows are reformatted as part of the data copy. This operation, however, does not change the table schema.
reorg rebuild – causes rows to be rebuilt as part of the data movement.
bcp bulk copy utility – supports tables that use in-row LOB columns. See “In-row LOB columns and bulk copy.”
You can migrate existing data using any of these methods, reducing space usage for the text pages and moving to in-row LOB storage.
