use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t (id int identity primary key, s varchar(8000));
insert into dbo.t
 (s)
 select top (10000)
  replicate('a', 5000)
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b
go

select
 c.name, pc.max_length
from
 sys.system_internals_partition_columns pc join
 sys.partitions p on p.partition_id = pc.partition_id left join
 sys.columns c on column_id = pc.partition_column_id and c.object_id = p.object_id
where
 p.object_id = object_id('dbo.t', 'U')
order by
 pc.partition_column_id;

exec sp_spaceused 'dbo.t', 'true';
go

alter table dbo.t alter column s varchar(6000);
go

select
 c.name, pc.max_length
from
 sys.system_internals_partition_columns pc join
 sys.partitions p on p.partition_id = pc.partition_id left join
 sys.columns c on column_id = pc.partition_column_id and c.object_id = p.object_id
where
 p.object_id = object_id('dbo.t', 'U')
order by
 pc.partition_column_id;

exec sp_spaceused 'dbo.t', 'true';
go

alter table dbo.t rebuild;
go

select
 c.name, pc.max_length
from
 sys.system_internals_partition_columns pc join
 sys.partitions p on p.partition_id = pc.partition_id left join
 sys.columns c on column_id = pc.partition_column_id and c.object_id = p.object_id
where
 p.object_id = object_id('dbo.t', 'U')
order by
 pc.partition_column_id;

exec sp_spaceused 'dbo.t', 'true';
go

drop table dbo.t;
go

use master;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create database DBTest001;
alter database DBTest001 set recovery full;
backup database DBTest001 to disk = 'nul:';
go

use DBTest001;
go

create table dbo.t1 (id int identity primary key, s varchar(1000));
create table dbo.t2 (id int identity primary key, s varchar(max));
create table dbo.t3 (id int identity primary key, s varchar(max));

exec sp_tableoption 'dbo.t3', 'large value types out of row', 1;
go

set statistics time on;
go

insert into dbo.t1
 (s)
 select top (100000)
  replicate('a', 1000)
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (s)
 select top (100000)
  replicate('a', 1000)
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t3
 (s)
 select top (100000)
  replicate('a', 1000)
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

set statistics time off;
go

use master;
alter database DBTest001 set single_user with rollback immediate;
drop database DBTest001;
go

 SQL Server Execution Times:
   CPU time = 422 ms,  elapsed time = 947 ms.

 SQL Server Execution Times:
   CPU time = 515 ms,  elapsed time = 961 ms.

 SQL Server Execution Times:
   CPU time = 5547 ms,  elapsed time = 6264 ms.