3

One of the (optional) requirements for the Works With SQL Server 2008 test is that row level compression is enabled on all tables and indexes. We have an existing database with a lot of tables and indexes already created. Is there an easy way to enable compression on all these tables and indexes?

Here is the script I ended up making from splattne's recommendation.

select 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'   
from   sysobjects   where  type = 'U' -- all user tables
UNION
select 'ALTER INDEX [' + k.name + '] ON [' + t.name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'
from   sysobjects k
join sysobjects t on k.parent_obj = t.id
   where  k.type = 'K' -- all keys
    AND t.type = 'U' -- all user tables

5 Answers 5

4

I've just used the Works With SQL Server Tool to test after compressing using the a_hardin-splattne script. The test failed because several indexes were not compressed.

The "sysobjects" view includes some but not all of the indexes. We need "sysindexes" instead. Thanks to the anonymous poster at aspfaq.com for this index insight. We also want to ignore user-defined functions.

SELECT 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM  sysobjects  WHERE type = 'U' -- all user tables
UNION
SELECT  'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(i.id) + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM 
    sysindexes i 
    inner join sysobjects o on o.name = OBJECT_NAME(i.id)
WHERE 
    (i.indid BETWEEN 1 AND 254) 
    AND (i.Status & 64)=0 
    AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 
    AND NOT o.type in ('TF','FN')
3

You could use this simple SQL script in order to create another script which should do the job:

   select 'ALTER TABLE ' + name + ' REBUILD WITH (DATA_COMPRESSION = ROW)' 
         + CHAR(13) + CHAR(10) + 'GO'
   from   sysobjects
   where  type = 'U' -- all user tables

(I didn't test this, but it should work.)


You can find a much more sophisticed script here on the SQLServerBible site (look for "db_compression procs".) Read the author's blog post "Whole Database - Data Compression Procs".

2
  • Thanks! The db_compression will come in great when we actually deploy, but for the sake of the testing we're doing I used a modified version of your script. It's in the original question for formatting. (The chars and GO weren't working for me.)
    – a_hardin
    May 25, 2009 at 21:26
  • Sorry, I forgot a single quote in my query. Run this in using "results to text" output in SQL Management Studio.
    – splattne
    May 26, 2009 at 6:25
3

As an aside, be careful with enabling everything to be compressed. The data is compressed in memory and decompressed every time it is accessed. For an OLTP system with lots of changes and memory-resident data, compression is not suitable as you'll burn more CPU for no gain in IOs. For data that is read-occasionally, like a data warehouse, it's much more suitable because you get a big trade-off in reduced IOs against the extra CPU. Compression is a data warehousing feature, not an OLTP feature. Not sure if this applies to you, but worth pointing out just-in-case, and for others reading the thread.

One other point - it may be that you don't get a significant gain from compression so it isn't worth turning out. Best practice to check the compression gain before enabling using the sp_estimate_data_compression_savings stored-proc.

Thanks

1

You should probably look to handle new tables as well, so you don't need to run this batch on a regular basis. I detailed a method for automatically compressing new tables in this blog post.

I'd also mention that you should check to see whether or not the table is compressed before rebuilding it.

0

I'm a little late to the party, but here's a version that uses DMVs rather than the deprecated system tables and allows for arbitrary schema names. It enables or disables row or page compression on all heaps, clustered indexes and nonclustered indexes (including all partitioned tables) in the current database:

-- Enables or disables compression on all tables in the database
DECLARE @Compression NVARCHAR(4) = 'PAGE' -- NONE, ROW or PAGE
    , @Cmd NVARCHAR(MAX) = '';

-- Clustered indexes, heaps
SELECT @Cmd +=  '
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');'
FROM sys.schemas s
    INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
WHERE p.[data_compression_desc] <> @Compression
    AND p.index_id IN (0, 1)
    AND NOT EXISTS (
        SELECT 1
        FROM sys.partitions d
        WHERE d.[object_id] = p.[object_id]
            AND d.index_id = p.index_id
            AND d.[partition_number] > p.[partition_number]
    );

-- Nonclustered indexes
SELECT @Cmd +=  '
ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');'
FROM sys.schemas s
    INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
    INNER JOIN sys.indexes i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
WHERE p.[data_compression_desc] <> @Compression
    AND p.index_id > 1
    AND NOT EXISTS (
        SELECT 1
        FROM sys.partitions d
        WHERE d.[object_id] = p.[object_id]
            AND d.index_id = p.index_id
            AND d.[partition_number] > p.[partition_number]
    );

-- Review commands
SELECT @Cmd;

-- Run commands
--EXEC sp_executesql @Cmd;
1
  • excelent job! script save. Only can add one ;\n to file script. Apr 25, 2023 at 15:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .