none
Is there a way to set the default compression level for all tables created in a database?

    Question

  • Is there a way to set the default compression level for all tables and indexes created in a specific database?

    We use a product that given an input table will create several output tables in the same database.  Testing has revealed that due to the nature of the data, and size of the tables we would see about a 50% disk space savings by compressing all the tables, without a significant impact on query performance.

    We can not change the product to create the tables with compression turned on. 

    Is there a way to turn on compression for tables and/or indexes on a database level?  I've searched and haven't been able to find one.

    If not we could alter and rebuild the tables with compression on after the product is finished. 



     




    Wednesday, February 18, 2009 7:35 PM

Answers

  • Jonathan and Jim are right - there is no way to do this using (out of the box) options.

    This would be a good time to use a DDL trigger though.....

    CREATE DATABASE CompressionTest  
    GO  
     
    USE CompressionTest  
    GO  
     
    ALTER TRIGGER CompressTablesAfterCreation  
    ON DATABASE 
    AFTER CREATE_TABLE  
    AS 
    BEGIN 
        DECLARE @TableSchema sysname, @TableName sysname      
        SELECT  @TableSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),              
                @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]''sysname')  
          
        DECLARE @SQL nvarchar(2000)  
          
        SET     @SQL =  'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' +   
                        QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION=PAGE)' 
          
        EXEC    (@SQL)  
    END 
    GO  
    CREATE TABLE CompressMePlease  
        (  
        Col1 int,  
        Col2 varchar(50)  
        )  
          
    --Check it out  
    SELECT OBJECT_NAME(object_id) AS [ObjectName], [rows], data_compression_desc, index_id  
    FROM sys.partitions  
    WHERE data_compression > 0  
    ORDER BY ObjectName;  
     
    --When you're done....  
    --DROP DATABASE CompressionTest 

    Any time you create a new table, the DDL trigger will "rebuild" it immediately with page compression (as part of the same transaction).

    I feel a blog article coming on....  ;-)

    HTH.
    Aaron Alton | thehobt.blogspot.com
    Thursday, February 19, 2009 3:41 PM

All replies

  • Assuming SQL Server 2008, yes you can use ALTER TABLE to compress and existing table:

    ALTER TABLE <tablename> REBUILD WITH (DATA_COMPRESSION=PAGE); 

    ALTER TABLE (Transact-SQL)

    The same applies to Indexes:

    ALTER INDEX <indexname> ON <tablename> REBUILD WITH (DATA_COMPRESSION=PAGE);  

    ALTER INDEX (Transact-SQL)
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, February 18, 2009 10:12 PM
  • I don't believe there is any way to set a global database default value for new tables, and there would be no option to automatically turn on compression for all tables and indexes due to the uncontrolled nature of this (transaction logs growing, etc).

    Instead, you can knock up a script that will get a list of all tables and indexes and execute the ALTER TABLE or ALTER INDEX commands that Jonathan suggested.

    Wednesday, February 18, 2009 10:38 PM
  • Jonathan and Jim are right - there is no way to do this using (out of the box) options.

    This would be a good time to use a DDL trigger though.....

    CREATE DATABASE CompressionTest  
    GO  
     
    USE CompressionTest  
    GO  
     
    ALTER TRIGGER CompressTablesAfterCreation  
    ON DATABASE 
    AFTER CREATE_TABLE  
    AS 
    BEGIN 
        DECLARE @TableSchema sysname, @TableName sysname      
        SELECT  @TableSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),              
                @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]''sysname')  
          
        DECLARE @SQL nvarchar(2000)  
          
        SET     @SQL =  'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' +   
                        QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION=PAGE)' 
          
        EXEC    (@SQL)  
    END 
    GO  
    CREATE TABLE CompressMePlease  
        (  
        Col1 int,  
        Col2 varchar(50)  
        )  
          
    --Check it out  
    SELECT OBJECT_NAME(object_id) AS [ObjectName], [rows], data_compression_desc, index_id  
    FROM sys.partitions  
    WHERE data_compression > 0  
    ORDER BY ObjectName;  
     
    --When you're done....  
    --DROP DATABASE CompressionTest 

    Any time you create a new table, the DDL trigger will "rebuild" it immediately with page compression (as part of the same transaction).

    I feel a blog article coming on....  ;-)

    HTH.
    Aaron Alton | thehobt.blogspot.com
    Thursday, February 19, 2009 3:41 PM
  • Hi guys,

    All's suggestions are good.

    Anyways,I think the trigger Aaron provided changes to "AFTER CREATE TABLE, ALTER TABLE" may be better due to "If not we could alter and rebuild the tables with compression on after the product is finished. ".

    Thanks,
    Jin 


    Jin Chen - MSFT
    Friday, February 20, 2009 3:25 AM
  • I wish the forums would send alerts more consistently than they do, or I would have replied to this a lot sooner.

    I'd have to agree that Aarons DDL trigger looks like a very good solution to your problem automagically.  I guess my only real question would be how are you creating tables, because if you use a SELECT INTO:

    SELECT *  
    INTO dbo.SelectIntoTest  
    FROM AdventureWorks.Sales.SalesOrderDetail 

    You get the following error:

    Msg 539, Level 16, State 78, Line 1
    Schema changed after the target table was created. Rerun the Select Into query.

    So you wouldn't be able to use it in that case, but otherwise I haven't been able to break it, which is always my first thing to test when dealing with triggers.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, February 20, 2009 3:42 AM
  • Thank you it looks like the triggers might work. 

    I am creating a couple tables later in the process with a select into, but if I have to I can change those.
    Friday, February 20, 2009 5:36 PM