sp_use_db_compression from the SAP team at Microsoft

drop proc sp_use_db_compression
go
create proc sp_use_db_compression @compression varchar(8) = NULL, @maxdop int = 0, @online varchar(6) = 'OFF',
@data_only varchar(10) = 'DATA', @batch_input int = 0, @verbose int = 0, @verbose_only int = 0,
@schemas varchar(3) = NULL, @offline_limit int = 1000, @batch_input_sort varchar(4) = 'ASC', @write_verbose_table int = 0,
@force_rebuild int=0
as
begin
/*
Procedure: sp_use_db_compression
Version 2.0 September 2008
Version 2.1 August 2009 Add output of SQL commands into table sp_use_db_compression_sql_commands
Version 2.2 August 2009 Fix issues with different schemas. Works now as described
                            Fix issues around verbose_only. All commands are printed now and batch_input table is not
                            populated in verbose_only anymore
Version 2.3 September 2009 Add option for S2K8 R2 to rebuild indexes or tables which already are compressed in the
compression method. Purpose is to allow applying UCS2 compression to all content of
a table
Plus some smaller fixes
Procedure should compress/decompress database tables either in or from ROW/PAGE compression
The procedure also can be used to decompress from any of the compression states. The procedure can be used in two differnt
modes:
1. Call the procedures as shown below without specifying batch_input mode. The procedure then will work on the current
   database and dependent on the input parameters will take all tables of the current schema or all tables of the
   current database (input parameter @schemas=ALL) as base of the desired activity. In this case the granularity (just data
   , just nc-indexes or data and nc-indexes) is applied to all the tables. As well the online or offline method of
   compression or decompression
2. One can prepopulate the table sp_use_db_compression_batch_input with a set of tables and activities desired and direct
   the procedure with the parameter @batch_input=1 to just use the tables specified in the batch_input table as base
   for the activities of compression and decompression. In this case one can define the granularity and method of
   compression and decompression on table basis. This mode is referred to as operating out of the batch_input table
There is a table called sp_use_db_compression_table where the complete activity is tracked. Querying that table
one always can see which table currently is worked on.
@compression - allowed values are NONE, ROW, PAGE. Any other value will result in a failure running
the stored procedure. NONE will be used for decompressing into normal non-compressed
state. This parameter is mandatory in case the parameter @verbose_only is not set.
Means for real execution the parameter is mandatory without batch_input table.
@maxdop - specifies the number of processors/core to be used for the compression/decompression. If not
specified, only one processor/core will be used. This parameter is not mandatory
@online - Specifies whether online of offline rebuild is taken. Allowed values are ON and OFF.
Be aware that not all tables can be done online due to restrictions in SQL server Engine.
In a case of a table not being able to be rebuild online, an entry is made in a table
called sp_use_db_compression_batch_input and flagged with Offline rebuild. The very same
table will be taken as source for batch_input. However there is an optimization for these tables
which need to be built offline. If a table is small enough we can compress/decompress it offline despite that
one asked for online rebuild. The default threshold is to do offline rebuilds on the fly for tabls
with less than 1000 data pages. however this threshold value can be set with the parameter @offline_limit
This applies for operating out of the batch_input table as well.
@data_only Specifies whether data, data+indexes or non-clustered indexes should be compressed. Allowed
parameter values are ALL, DATA and NCINDEX .The parameter is not mandatory and
its default is data only = DATA
@batch_input Is an integer value. Not defining it or defining it with the value 0 will be treated as not
reading the table to be compressed out the the table sp_use_db_compression_batch_input but
from the database. This usually ends up compressing the whole database. In case of a subset
of tables should be compressed only, one can use this option and populate the table
sp_use_db_compression_batch_input with the tables to be compressed. The table might not exist
yet and can be created in the schema the stored procedure runs with this command:
create table sp_use_db_compression_batch_input (Tab_Name sysname, Target_Type varchar(20),
Compression_Type varchar(8), [Online] varchar(6), Status varchar(20));
the target_type column will map into the @data_only parameter and will overwrite what got specified
calling the stored procedure. The compression_type column will define whether it is ROW or Page compression
or decompression shoud be done. The content on the column online will overwrite the @online
parameter which was specified calling the stored procedure. Means naming the two parameters
calling the stored procedure is meaningless. After having compressed a table listed by an
entry in this table, the entry gets updated in the Status field. The Status column needs to be set to
'TO_BE_DONE' in order to have the table picked up. The Status field will change to 'IN_WORK' while the
table gets compressed/decompressed and then will be changed to 'DONE' when the table has been finished
@verbose prints out additional status messages telling the table it works on. It needs to be 1 to activate
@verbose_only is providing dry run capabilities by doing all the steps, but not executing the compression/decompression.
It could be used to get the statements to execute as result set. The value needs to be set to 1
to activate. If this is combined with the batch_input mode, the status field remains untouched, however
tables which need to be rebuilt in the offline method will be changed in the compression_type field
of sp_use_db_compression_batch_input from ON to OFF
@schemas Set to NULL is default. Means that only the current schema (schema in which procedure got created in is done)
To take all tables in all schemas, the value needs to be set to 'ALL'. Restriction is the accessibility
of the other schemas within the database. The user who issues the command needs to be in the dbo role
for the specific database in order to even see the content of the other schemas and work on the content of
the other schemas.
@offline_limit Default is 1000. This parameter is used on the case of online compression/decompression. Not all tables
can be done online (see description of @online parameter). E.g. tables with BLOBs can't be done online
and need to be done offline. For tables with less pages than defined with this parameter the compression
or decompression is done offline on the fly despite asking for online operations instead of noting the
tables in the batch_input table for later processing. Rationale behind it is that a small table can
be done offline w/o hampering the application. Dependent on the conditions, this parameter can be
adjusted to higher or lower values
@batch_input_sort
In case of not working out of the batch_input table, we assume that with specifying ROW or PAGE as compression
types with the @compression parameter, we need to perform serious compression. Hence we build a cursor over the
tables of the schema or database (see parameter @schemas) and sort by the table sizes in an ascending manner.
Means we try to leverage the small freespace we eventually have in the database to compress smaller tables first
and create space for the larger tables. When @compression is set to NONE we sort exactly the other way round so
that we decompress the largest tables first. This all works fine when we don't operate in the batch_input mode.
Problem is that we can't know really what activities are asked for in the batch_input table. One
can even mix compression and decompression on tables. Hence we don't know whether to sort ascending to the size
(preferable for compressing) or descending to the size (preferable to decompress). This parameter
is a possibility to define sort ascending or descending when working with an input table. The default is to
sort with ascending size. this would assume compression activity
@write_verbose_table
In combination with @verbose_only, the T-SQL commands to compress or decompress are written into a table. The table
is named sp_use_db_compression_sql_commands. In order to preserve the order which was chosen by the procedure the
table has a Rnumber column which basically docuemnts the order in which the commands got inserted. A second column
gives the T-SQL command. If the option is set to a value <>0, but @verbose_only was not set explicitely the
procedure will stop with an error message. The table is getting created if it doesn't exist. It also is getting
truncated between different runs
@force_rebuild
Added for the specific case of UCS2 compression in SQL Server 2008 R2. Assuming you have a S2K8 database with
compressed tables and attach it to S2K8 R2, you get all new rows inserted or modified in the new UCS2 compressed
way. Only in case of compressed tables though. In order to compress the older content of the tables, we need to
the rebuild. Without this flag the logic of the procedure will not rebuild indexes or tables which already are
compresssed in the same way as it should compress.Means in order to get UCS2 compression deployed to an already
compressed table, this parameter is necessary

The normal procedure compressing a whole database with ROW compression would look like starting the procedure
with the following call:
sp_use_db_compression ROW, @maxdop=1,  @online= 'ON', @verbose=1
This would compress all the data but no non-clustered indexes in ROW compression using the online method and using one CPU.
After this run passed, one can check run times and before and after sizes of the tables in the table
sp_use_db_compression_table. Since it could happen (in SAP databases of serious size certainly will happen) that
some tables can't be compressed in an online manner, it is expected that the statement:
select * from sp_use_db_compression_batch_input
will return some rows. These entries specify tables which were not yet compressed, because the requested online compression
is not valid due to data types used in those tables. These tables now need to be compressed offline. The offline compression
can be executed by calling this stored procedure again with this command
sp_use_db_compression ROW, @maxdop=1, @verbose=1, @batch_input=1
In this case the data out of sp_use_db_compression_batch_input is read and the compression is executed in the mode
requested in the table, which in this scenario would be offline. The call with @batch_input=1 as parameter doesn't require
specifying @online since it anyway will be overwritten by what is defined in the table. Decompression or compression to PAGE
compression will work the same way

Another possibility which got introduced in 2.1 is to simply write the T-SQL commands which would be executed into
a table. The table's name is sp_use_db_compression_sql_commands. The call to the procedure would look like
sp_use_db_compression ROW, @maxdop=1, @verbose_only=1, @write_verbose_table=1
For this one the @verbose_only and @write_verbose_table need to be set. In case of using the online option, the command
for a table which can't be rebuild in an online manner would get written with offline rebuild option.

The design of first operating and specifying on a table level and then drill down into looking for indexes seems
to make the coding overly complicated. However user feedback was that they would like to select tables and then
decide in a global switch whether to perform the activity on data level (heap or clustered index), non-clustered indexes
only or the complete table with all indexes. We got a lot of push back when we suggested to enter single indexes into the
batch_input table. Users only wanted to define the table names and then on a per table basis select what to compress.
This in all honesty didn't make the design easier.
*/
set nocount on;
-- Let's check all the inputs first
-- Transform @compression in upper case to enable comparisions afterwards
-- Need to check on verbose_only first

select @compression=UPPER(@compression);
-- if no Compression type is set and batch_input is not activated, escape
if ((upper(@compression) not in ('NONE','ROW', 'PAGE')) and (@batch_input=0))
begin
print 'Compression parameter needs to be specified';
return;
end

if ((@compression is NULL) and (@batch_input=0))-- Handle NULL case separately
begin
print 'Compression parameter needs to be specified';
return;
end
-- if maxdop is not defined, let's stay conservative and set to serial execution
if (@maxdop is NULL)
set @maxdop = 1;

-- if online is not specified, set to online since it will be less intrusive
if @online is not NULL
select @online = UPPER(@online);
if @online not in ('ON', 'OFF') or (@online is NULL)
set @online = 'ON';

-- if data_only is not specified, set to DATA
if @data_only is not NULL
select @data_only = UPPER(@data_only);
if @data_only not in ('DATA', 'ALL', 'NCINDEX')
begin
print 'Compression parameter is unknown. Only DATA, ALL and NCINDEX are accepted values';
return;
end

-- if data_only is not specified, set to DATA
if (@data_only is NULL) -- Handle NULL case separately
set @data_only = 'DATA';
-- if verbose is >0, set to on
if (@verbose) >0
set @verbose = 1;

-- -- if verbose is <0 null="" off="" or="" p="" set="" to="">if (@verbose <0 is="" nbsp="" null="" or="" p="" verbose=""> set @verbose = 0;

-- if verbose_only is >0, set to on
if (@verbose_only) >0
set @verbose_only = 1;

-- -- if verbose_only is <0 null="" off="" or="" p="" set="" to="">if (@verbose_only <0 is="" nbsp="" null="" or="" p="" verbose_only=""> set @verbose_only = 0;

-- if batch_input is not 0, set to 1
if (@batch_input) <>0
set @batch_input = 1;

-- Check @schema, anything else than ALL will be changed to NULL
select @schemas=UPPER(@schemas)
if (@schemas) <> 'ALL'
set @schemas = NULL;

--Check @offline_limit for corect range of >0
if (@offline_limit <1 p=""> set @offline_limit = 1000;

-- Check parameter @batch_input_sort
if (UPPER(@batch_input_sort) not in ('ASC', 'DESC'))
set @batch_input_sort = 'ASC';
-- Check parameter force_rebuild
if @force_rebuild <>0
set @force_rebuild=1;

-- Check on parameter @write_verbose_table Parameter only allowed in combination with @verbose_only
if @write_verbose_table<>0 and @verbose_only=0
begin
print 'The option @write_verbose_table only is allowed together with @verbose_only=1';
return(1);
end

-- Check on parameter @write_verbose_table Parameter, creater table needed or truncate existing table
-- define a first variable needed now
declare @rownumber int
if @write_verbose_table<>0 and @verbose_only=1
begin
if not exists (select name from sys.tables where name = 'sp_use_db_compression_sql_commands'
and schema_id = schema_id())
begin  --create table needed to store SQL Commands
create table sp_use_db_compression_sql_commands (Rnumber int not null, SQL_Command nvarchar(4000))
with (DATA_COMPRESSION=PAGE);
print 'Created table sp_use_db_compression_sql_commands'
end
else  -- table exists - truncate table
begin
truncate table sp_use_db_compression_sql_commands;
print 'Truncated table sp_use_db_compression_sql_commands';
end
-- set row number count to 1
set @rownumber=1;
end

--create log table in current schema
if NOT exists (select name from sys.tables where name = 'sp_use_db_compression_table' and schema_id = schema_id())
begin
create table sp_use_db_compression_table ([Run] int NOT NULL, Date datetime NOT NULL, DBname sysname NOT NULL,
Tab_Name sysname NOT NULL, Max_Row_Len int, Compression_Time int, Row_Count bigint, Before_Reserved bigint,
After_Reserved bigint, Before_Pages bigint, After_Pages bigint, Before_Used bigint, After_Used bigint,
Before_DC_Type varchar(20), After_DC_Type varchar(20)) with (DATA_COMPRESSION=PAGE);
create clustered index sp_use_db_compression_table_cl on sp_use_db_compression_table (Run, DBname, Tab_Name)
with (DATA_COMPRESSION=PAGE);
end

-- Start section which defines the structures to keep excluded tables.

declare @sp_use_db_compression_excludes table (Tab_Name sysname, [None] varchar(1), Row varchar(1), Page varchar(1));

-- This section needs to be defined for each of the Applications. In this case we are looking at the tables with the
-- SAP ABAP stack.
-- Populate the table variable. This shows an example for typical SAP databases
-- Exclude tables starting with 'sap' from any compression
insert into @sp_use_db_compression_excludes select name, 'X', 'X', 'X' from sys.tables where name like 'sap%';
-- Exclude tables starting with 'sp' from any compression
insert into @sp_use_db_compression_excludes select name, 'X', 'X', 'X' from sys.tables where name like 'sp%';
-- for Page compression exclude some more tables
insert into @sp_use_db_compression_excludes select name, NULL, NULL, 'X' from sys.tables where name in ('VBDATA', 'VBHDR', 'VBMOD, ARFCSTATE', 'ARFCSDATA','TRFCQDATA', 'TRFCQIN',
'TRFCQINS', 'TRFCQOUT', 'TRFCQSTATE', 'ARFCRDATA' , 'ARFCRSTATE');
-- end of definition of exclude tables


declare @tname sysname, @id int, @indid int, @reserved bigint, @reserved_after bigint, @dpages bigint, @dpages_after bigint;
declare @used bigint, @used_after bigint, @counter int, @exes varchar(255), @maxrlen int, @rowcnt bigint;
declare @dc_type varchar(20), @dc_type_after varchar(20), @run int, @dbname sysname, @action varchar(4);
declare @times1 datetime,@times2 datetime,@times3 int, @online_overwrite varchar(8), @indname sysname;
declare @data_only_org varchar(10), @online_org varchar(6), @schema sysname, @index_count int;


--First handle the non batch_input mode and distinguish between compression and decompression from a sort order
-- get table data and sort it by size ascending. Easier to get the sizes out of the old sysindexes view.
-- Sort is done ascended to slowly increase free space in the hope that no additional extension of the
-- database is needed
if (@batch_input =0)
begin
if @compression in ('ROW', 'PAGE') -- compression is done for small table first to create space for large tables
begin
-- if current schema should be worked on only
if @schemas is NULL
declare c1 cursor forward_only read_only for select so.name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, ss.name
from sysindexes si, sys.objects so, sys.partitions sp, sys.schemas ss
where si.indid in (0,1) and so. type='U' and so.object_id=si.id and so.object_id=sp.object_id and si.indid=sp.index_id
and sp.partition_number=1 and so.schema_id= ss.schema_id and so.schema_id=schema_id()
order by si.reserved ASC;
else   -- @schemas = 'ALL' should be true. Hence we don't limit on current schema, but all what the user can read
declare c1 cursor forward_only read_only for select so.name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, ss.name
from sysindexes si, sys.objects so, sys.partitions sp, sys.schemas ss
where si.indid in (0,1) and so. type='U' and so.object_id=si.id and so.object_id=sp.object_id and si.indid=sp.index_id
and sp.partition_number=1 and so.schema_id= ss.schema_id
order by si.reserved ASC;
end
else  -- we compresion is 'NONE' Hence we want to decompress largest table first Sort by reserved desc
begin
-- only tables in current schema should be accepted
if @schemas is NULL
declare c1 cursor forward_only read_only for select so.name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, ss.name
from sysindexes si, sys.objects so, sys.partitions sp, sys.schemas ss
where si.indid in (0,1) and so. type='U' and so.object_id=si.id and so.object_id=sp.object_id and si.indid=sp.index_id
and sp.partition_number=1 and so.schema_id= ss.schema_id and so.schema_id=schema_id()
order by si.reserved DESC;
else   -- @schemas = 'ALL' should be true. Hence we don't limit on current schema, but all what the user can read
declare c1 cursor forward_only read_only for select so.name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, ss.name
from sysindexes si, sys.objects so, sys.partitions sp, sys.schemas ss
where si.indid in (0,1) and so. type='U' and so.object_id=si.id and so.object_id=sp.object_id and si.indid=sp.index_id
and sp.partition_number=1 and so.schema_id= ss.schema_id
order by si.reserved DESC;
end
end
else  -- we read the tables to be compressed out of sp_use_db_compression_batch_input.
begin
-- only tables in current schema should be accepted
if @schemas is NULL
begin
if (@batch_input_sort = 'ASC')
declare c1 cursor forward_only read_only for select so.Tab_Name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, so.Compression_Type, so.[Online], so.Target_Type, ss.name
from sysindexes si, sp_use_db_compression_batch_input so, sys.partitions sp, sys.objects soo, sys.schemas ss
where si.indid in (0,1) and soo. type='U' and object_id(so.Tab_Name)=si.id and object_id(so.Tab_Name)=sp.object_id
and si.indid=sp.index_id and object_id(so.Tab_Name)=soo.object_id and so.Status = 'TO_BE_DONE'
and sp.partition_number=1 and soo.schema_id=schema_id() and ss.schema_id=soo.schema_id
order by reserved ASC
else
declare c1 cursor forward_only read_only for select so.Tab_Name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, so.Compression_Type, so.[Online], so.Target_Type, ss.name
from sysindexes si, sp_use_db_compression_batch_input so, sys.partitions sp, sys.objects soo, sys.schemas ss
where si.indid in (0,1) and soo. type='U' and object_id(so.Tab_Name)=si.id and object_id(so.Tab_Name)=sp.object_id
and si.indid=sp.index_id and object_id(so.Tab_Name)=soo.object_id and so.Status = 'TO_BE_DONE'
and sp.partition_number=1 and soo.schema_id=schema_id() and ss.schema_id=soo.schema_id
order by reserved DESC
end
else  -- Assume @schemas=ALL is set
begin
if (@batch_input_sort = 'ASC')
declare c1 cursor forward_only read_only for select so.Tab_Name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, so.Compression_Type, so.[Online], so.Target_Type, ss.name
from sysindexes si, sp_use_db_compression_batch_input so, sys.partitions sp, sys.objects soo, sys.schemas ss
where si.indid in (0,1) and soo. type='U' and object_id(so.Tab_Name)=si.id and object_id(so.Tab_Name)=sp.object_id
and si.indid=sp.index_id and object_id(so.Tab_Name)=soo.object_id and so.Status = 'TO_BE_DONE'
and sp.partition_number=1 and ss.schema_id=soo.schema_id
order by reserved ASC
else
declare c1 cursor forward_only read_only for select so.Tab_Name, si.indid, si.name, si.reserved, si.used,
si.dpages, sp.data_compression_desc, so.Compression_Type, so.[Online], so.Target_Type, ss.name
from sysindexes si, sp_use_db_compression_batch_input so, sys.partitions sp, sys.objects soo, sys.schemas ss
where si.indid in (0,1) and soo. type='U' and object_id(so.Tab_Name)=si.id and object_id(so.Tab_Name)=sp.object_id
and si.indid=sp.index_id and object_id(so.Tab_Name)=soo.object_id and so.Status = 'TO_BE_DONE'
and sp.partition_number=1 and ss.schema_id=soo.schema_id
order by reserved DESC
end
end

open c1;
if @batch_input = 0
fetch c1 into @tname, @indid, @indname, @reserved, @used, @dpages, @dc_type, @schema;
else
fetch c1 into @tname, @indid, @indname, @reserved, @used, @dpages, @dc_type, @compression, @online, @data_only, @schema;

--Save origin value of @data_only since we manipulate it eventually
set @data_only_org = @data_only;

--Save origin value of @online since we manipulate it eventually
set @online_org = @online;

-- set online_overwrite on same level as @online to avoid a mismatch of values in later processing
set @online_overwrite = @online;
set @exes = ''  -- set execution string empty
-- get current database
select @dbname =db_name();
-- get maximum run id out of sp_use_db_compression_table
select @run=MAX([Run])+1 from sp_use_db_compression_table;
-- if table is empty, set run ID to 1
if @run is NULL
set @run =1;
while @@fetch_status <>-1
begin
set @action='GO'; -- set switch for activity control to positive
-- filter table names in order to allow definition of exception tables for different type of compressions in case
if (@compression= 'PAGE') and exists (select Tab_Name from @sp_use_db_compression_excludes where Tab_Name =@tname
                                     and Page ='X')
begin
set @action='NONE'; -- if table is on exception list, do nothing
end
-- filter exception list for row compression
if (@compression = 'ROW') and exists (select Tab_Name from @sp_use_db_compression_excludes where Tab_Name =@tname
 and Row ='X')
set @action='NONE';
-- filter for decompression
if (@compression = 'NONE') and exists (select Tab_Name from @sp_use_db_compression_excludes where Tab_Name =@tname
and None ='X')
set @action='NONE';
   
    -- Merge table and schema name into @tname to make the whole thing work with schemas - correction in 2.2
    if @batch_input = 0  -- not for batch_input since table names should be written with schema added
select @tname = @schema + '.' + quotename(@tname);
-- compare old and new compression state. if compression state and desired compression are the same on heap or clustered index
-- and rebuild is not forced, rebuild should do nothing
if (@compression=@dc_type) and (@data_only = 'DATA') and (@force_rebuild=0)
set @action='NONE';
-- more tricky. If data+index needs to be compressed, we need to check whether there are indexes which are not compressed.
-- Now we need to check whether all indexes have the same level of compression. If not we need to
-- rebuild those additional indexes. Only for case where rebuild is not forced. In case of rebuild forced,
-- we rebuild the whole table
if (@data_only = 'ALL') and (@force_rebuild=0) and @action = 'GO'
begin
-- check for indexes which are not on the requested compression level
select @index_count= count(*) from sys.partitions sp
where sp.data_compression_desc <> @compression and sp.object_id=object_id(@tname);
if (@index_count=0)
set @action='NONE'; -- no further action required because either no indexes are there or all are on the requrested compression level
-- if index_count which are not on the requested level are not even with existing indexes of the table and we still need
-- to continue checks
if (@index_count <> (select count(*) from sys.partitions sp where sp.object_id=object_id(@tname)))
and @action = 'GO'
begin
-- Hate to do it, but overwrite the value of data_only to one which is not allowed to be called. Simply don't
-- want to break the whole procedure into index granularity. But we need to indicate that some of the indexes
-- have a different compression for later in the logic
set @data_only = 'INDEX';
set @action='GO'; -- Continue
end
end
-- if online option is chosen we need to check whether we can even rebuild the table online. Tables which
-- should be rebuild online may not have image, text,ntext,varbinary(max) or (n)varchar(max) columns
if ((@online = 'ON') and (@action='GO'))
begin
-- check for columns with data types which don't allow online index builds
if exists (select * from sys.columns where object_id=OBJECT_ID(@tname) and user_type_id in (34,35,99) or
(object_id=OBJECT_ID(@tname) and user_type_id in (165,167,231,241) and max_length=-1))
begin
-- if more than # data pages defined with @offline_limit we put it in exception list,
-- otherwise let's do it online only takes a few seconds
if @dpages > @offline_limit  -- default = 1000
begin
-- check whether exception table is around already in current schema
if not exists (select name from sys.tables where name = 'sp_use_db_compression_batch_input'
and schema_id = schema_id())
begin
-- if not create it in current schema
create table sp_use_db_compression_batch_input (Tab_Name sysname, Target_Type varchar(20),
Compression_Type varchar(8), [Online] varchar(6), Status varchar(20)) with (DATA_COMPRESSION=PAGE);
create clustered index batch_input on sp_use_db_compression_batch_input(Tab_Name, Status)
with (DATA_COMPRESSION=PAGE);
end
-- if entry with table name already exists, delete old entry
if exists (select Tab_Name from sp_use_db_compression_batch_input where Tab_Name = @tname)
begin
delete from sp_use_db_compression_batch_input where Tab_Name = @tname;
if (@verbose =1) and (@verbose_only = 0) --print only if verbose is on and verbose_only is off
print 'Old record for table ' + @tname + ' deleted in sp_use_db_compression_batch_input';
end
if (@verbose_only=0) --only insert if verbose_only=0 - means real run
insert into sp_use_db_compression_batch_input values (@tname, @data_only_org, @compression, 'OFF', 'TO_BE_DONE');
if (@verbose =1) and (@verbose_only = 0) --print only if verbose is on and verbose_only is off
print 'At least one table could not be rebuild in online fashion. Please check table ' +
       'sp_use_db_compression_batch_input in your current schema'
-- Specify that no further action is taken on this table if @dpages > @offline_limit at and verbose_only
-- is not set. If @verbose_only is set then try to print command, however with changing online to offline
if @verbose_only=0
set @action='NONE';
else  -- @verbose_only is activated hence we want to continue, but change @online to OFF
begin
set @action='GO';
select @online = 'OFF';
end
end
else
begin
-- means table has columns which block online rebuild, but table is 1000 data pages or less.
-- Hence overwrite Online compression option
select @online = 'OFF';
end
end
end
If @action='GO'
begin
select @times1= GETDATE();
--insert row into sp_use_db_compression_table in case of verbose_only =0
if @verbose_only=0
insert into sp_use_db_compression_table values (@run, getdate(), @dbname, @tname, NULL, NULL, NULL,
@reserved, NULL, @dpages, NULL, @used, NULL, upper(@dc_type),
NULL);
if @verbose_only=0 and @batch_input=1  -- update Status to IN_WORK in batch_input table
update sp_use_db_compression_batch_input set Status = 'IN_WORK' where Tab_Name = object_name(object_id(@tname))
and [Online] = @online_org;   -- need to use @online_org since @online is overwritten in case of offline optimization
-- execute only if current compression type is different from new one
-- build execution string
-- if we are dealing with a heap. Command by be followed by a second command
if ((@indid=0) and (@data_only not in ('INDEX', 'NCINDEX')))
select @exes = 'alter table ' + @tname + ' rebuild with (data_compression = ' + @compression +
', maxdop = ' + convert(varchar,@maxdop) + ', online = ' + @online + '); ';
-- only if ALL is defined with @data_only
-- Independent whether indid=0 or indid=1 add this statment when we want data+index
-- If table is clustered, all indexes including the clustered index are done
-- if table is a heap with additional non-clustered indexes, additional non-clustered indexes are done here after
-- heap was done above already
if @data_only = 'ALL'
begin
--if table with clustered index and data = 'ALL', go for it
if (@indid=1)
select @exes = @exes + 'alter index ALL on ' + @tname + ' rebuild with (data_compression = ' + @compression +
', maxdop = ' + convert(varchar,@maxdop) + ', online = ' + @online + ');';
else -- assume @indid=0. Now we need to check whether there are additional indexes on the heap table
begin
if exists (select name from sys.indexes where index_id > 0 and object_id=object_id(@tname))
select @exes = @exes + 'alter index ALL on ' + @tname + ' rebuild with (data_compression = ' + @compression +
', maxdop = ' + convert(varchar,@maxdop) + ', online = ' + @online + ');';
end
end
-- if we want data only, we only need to handle the case of the clustered index. Heap already got handled above
if ((@indid = 1) and (@data_only = 'DATA')) -- indid=0 we handled above already. Now we handle the DATA-only case of the clustered index
select @exes = @exes + 'alter index ' + quotename(@indname) + ' on ' + @tname + ' rebuild with (data_compression = ' + @compression
+ ', maxdop = ' + convert(varchar,@maxdop) + ', online = ' + @online + ');';
--independent of heap or clustered case. We need to deal with specific indexes only.
-- Also includes compression/decompression on non-clustered indexes only
if (@data_only = 'INDEX' or @data_only = 'NCINDEX')
begin
-- distinguish the case between any index clustered and non-clustered and non-clustered only in the cursor
if (@data_only='INDEX')  -- only could get into this state with @force_rebuild=0
-- get name of the indexes which are compressed differently to heap or clustered index
declare c2 cursor forward_only read_only for select si.name from sys.partitions sp,
sys.indexes si where sp.data_compression_desc <> @compression and sp.object_id=object_id(@tname)
and si.object_id=sp.object_id and si.index_id = sp.index_id order by si.index_id asc;
if (@data_only='NCINDEX') and (@force_rebuild=0)
-- get name of the non-clustered indexes with different compression than requested one
declare c2 cursor forward_only read_only for select si.name from sys.partitions sp,
sys.indexes si where sp.data_compression_desc <> @compression and sp.object_id=object_id(@tname)
and si.object_id=sp.object_id and si.index_id = sp.index_id and si.index_id >1;
if (@data_only='NCINDEX') and (@force_rebuild=1)
-- get name of the non-clustered indexes independent of current compression
declare c2 cursor forward_only read_only for select si.name from sys.partitions sp,
sys.indexes si where sp.object_id=object_id(@tname)
and si.object_id=sp.object_id and si.index_id = sp.index_id and si.index_id >1;
open c2
fetch c2 into @indname
while @@fetch_status<>-1
begin
select @exes = 'alter index ' + quotename(@indname) + ' on ' + @tname + ' rebuild with (data_compression = ' + @compression +
', maxdop = ' + convert(varchar,@maxdop) + ', online = ' + @online + ');';
if @verbose_only=1 -- print only when in dry run mode. Gives the statement text
begin
print @exes;
if @write_verbose_table<>0
begin
insert into sp_use_db_compression_sql_commands values (@rownumber, @exes);
select @rownumber=@rownumber+1;
end
end
-- execute compression if verbose_only=0 - No dry run
if @verbose_only=0
begin
if (@verbose =1) and (@verbose_only = 0) --print only if verbose is on and verbose_only is off
print 'Working on table ' + @tname + ' and Index ' + @indname + ' with maxdop = ' + convert(varchar,@maxdop) + ' option online = ' + @online;
exec (@exes);
end
fetch c2 into @indname;
end
deallocate c2;
end
if @data_only not in ('INDEX', 'NCINDEX') -- only if we don't have to handle specific indexes
begin
if (@verbose =1) and (@verbose_only = 0) --print only if verbose is on and verbose_only is off
print 'Working on table ' + @tname + ' with maxdop = ' + convert(varchar,@maxdop) + ' option online = ' + @online;
if @verbose_only=1 -- print only when in dry run mode. Gives the statement text
begin
print @exes;
if @write_verbose_table<>0
begin
insert into sp_use_db_compression_sql_commands values (@rownumber, @exes);
select @rownumber=@rownumber+1;
end
end
-- execute compression if verbose_only=0 - No dry run
if @verbose_only=0
exec (@exes);
end
-- restore origin value for @data_only
set @data_only= @data_only_org;
select @times2= GETDATE();
select @times3=convert(int, DATEDIFF(ms,@times1,@times2));
-- Get new actual size
select @reserved_after = si.reserved, @dpages_after = si.dpages, @used_after=si.used,
@rowcnt=rowcnt from sys.objects so, sysindexes si
where so.object_id=si.id and si.indid in (0,1) and so.object_id = object_id(@tname);
-- select new compression type
select @dc_type_after=@compression;
select @maxrlen = sum(max_length) from sys.columns where object_id=object_id(@tname);
-- update data in table in case of not verbose_only
if @verbose_only=0
update sp_use_db_compression_table set Max_Row_Len=@maxrlen, Compression_Time=@times3, Row_Count=@rowcnt,
After_Reserved=@reserved_after,After_Pages=@dpages_after,After_Used=@used_after, After_DC_Type=upper(@dc_type_after)
where Run=@run and DBname=@dbname and Tab_Name=@tname;
end
-- fetch next table
if @batch_input = 0
begin
fetch c1 into @tname, @indid, @indname, @reserved, @used, @dpages, @dc_type, @schema;
--Save origin value of @data_only since we manipulate it eventually
set @online = @online_org;
end
else  -- assume @batch_input parameter was set to 1
begin
-- invalidate table entry out of batch_input table. Need to have the current compression type in the
-- where clause in order not to invalidate the new row which eventually could have been inserted
-- because the compression couldn't be executed in the online mode
if (@verbose_only =0)  -- only in case of real action. Not for verbose_only run
update sp_use_db_compression_batch_input set Status = 'DONE' where Tab_Name = object_name(object_id(@tname))
and [Online] = @online_org;   -- need to use @online_org since @online is overwritten in case of offline optimization
fetch c1 into @tname, @indid, @indname, @reserved, @used, @dpages, @dc_type, @compression, @online, @data_only, @schema;
--Save origin value of @data_only since we manipulate it eventually
set @data_only_org = @data_only;
--Save origin value of @online since we manipulate it eventually.
set @online_org = @online;
end
set @exes = ''  -- reset execution string
end  --end of cursor loop
deallocate c1;
end