Here's a theoretical situation;

So how can we add compression? One solution is to use ddl triggers; ddl triggers are executed with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS. So by creating a ddl trigger that fires on CREATE INDEX to alter the index and rebuild the index as page compression, we can get optimal compression on the tables.

Clearly, this is very situational:


--create a dummy table to play around with
CREATE TABLE CompressTest
 (
 Col1 INT,
 Col2 VARCHAR(50),
 Col3 INT
 )
 GO

-- ddl trigger; triiger executes post "create index" operation
-- checks that the index created is clustered or non clustered
-- if it is it immediately rebuilds it with page compression
CREATE TRIGGER trgr_CompressIndexPostCreate
ON DATABASE
AFTER CREATE_INDEX
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @TableSchema sysname
 DECLARE @TableName sysname
 DECLARE @IndexName sysname
 DECLARE @sql NVARCHAR(1024)
 SELECT @TableSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname')
 SELECT @TableName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname')
 SELECT @IndexName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
 IF EXISTS (select t.name, i.name
 from sys.tables t
 inner join sys.indexes i on i.object_id = t.object_id
 where t.name = @TableName
 and i.name = @IndexName
 and i.type IN (1, 2) --clustered or non clustered
 )
 BEGIN
 SET @sql = 'ALTER INDEX ' +QUOTENAME(@IndexName)+ 'ON' +
 QUOTENAME(@TableSchema) + '.' +
 QUOTENAME(@TableName) + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION=PAGE);'
 EXEC (@sql);
 END
END
GO

-- create clustered index to test

CREATE CLUSTERED INDEX [ClusteredIndex_col1] ON [dbo].[CompressTest]
(
 [Col1] ASC
)
GO

-- create non-clustered to test
CREATE NONCLUSTERED INDEX [NonClusteredIndex_Col2] ON [dbo].[CompressTest]
(
 [Col3] ASC
)
GO

-- how we check this
select distinct t.name, i.name
 from sys.tables t
 inner join sys.partitions p on p.object_id = t.object_id
 inner join sys.indexes i on i.object_id = t.object_id
 where i.type IN (1, 2)
 and p.data_compression > 0

--tidy up, because leaving triggers around is rarely a good idea
DROP TABLE CompressTest

DROP TRIGGER trgr_CompressIndexPostCreate ON DATABASE

As I've already said, there is a risk here adding this to any/all databases: