1
  1. Is there a way to create a SQL Server 2014 database so that all data tables will be compressed automatically?

  2. Is there a way to compress all the existing tables of a SQL Server 2014 database?

Background: we run a data-gathering program that writes several multi-million row tables into a database which is then copied to a separate computer for offline analysis and reporting, often in a different physical location. The separate computer is not as powerful as the data-gathering computer, and has limited disk space, so the database size is an issue.

Edit: We use SQL Server 2014 Enterprise. Each table has a primary key and two additional indexes.

  • Which edition of SQL Server are you on? Standard? Enterprise? Something else? – Erik Darling Apr 23 '17 at 20:57
  • Policy manager could be used to force this behavior or at least report on it to you, and it doesn't require triggers. You could set a schedule for it to run against and if it finds anything, it can report it in to you. I haven't used Policy Management to force SQL Server to change the schema as an action but it might be possible. This way if someone makes a table policy management would force the schema to change. This seems like the wrong way of doing it though, if possible train the devs. A trigger is not a bad idea but I personally avoid them if I can. – Ali Razeghi Apr 24 '17 at 17:21
1

I'm not aware of a way to automatically compress new tables. The script below will compress existing tables and indexes that are not already compressed. You could schedule this periodically to compress new tables if you don't have control over the table and index create DDL.

DECLARE @SQL nvarchar(MAX) = (
    SELECT
          N'ALTER '
          + CASE
                WHEN type_desc ='HEAP' 
                THEN 'TABLE ' 
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
                ELSE 'INDEX ' 
                + QUOTENAME(name)
                + N' ON '
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
        END
        + N' REBUILD WITH(DATA_COMPRESSION=ROW);
'
    FROM sys.indexes
    WHERE
        OBJECTPROPERTYEX(object_id, 'IsMSShipped') = 0
        AND EXISTS(
            SELECT 1
            FROM sys.partitions
            WHERE
                partitions.object_id = indexes.object_id
                AND partitions.index_id = indexes.index_id
                AND data_compression_desc = N'NONE'
            )
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
EXEC sp_executesql @SQL;

Note that the compression feature is only available in Enterprise and Developer Editions in SQL Server 2014 and earlier versions. It is also available in Standard Edition beginning with SQL Server 2016 SP1.

  • 4
    You could do this in a DDL trigger on CREATE TABLE / CREATE INDEX. – Aaron Bertrand Apr 23 '17 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.