Defaulting Data Compression on Creating Indexes
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: