Compress SQL Server database

Question

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.

Answer 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.