Compress SQL Server database
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.