This is a simple script that’s held on my website mostly for reference as it simply takes any table and then compresses it with PAGE level compression.
If you want ROW level then simply substitute in the code, but this was a script I needed recently and therefore wanted accessible via my website and therefore it’s on here but for PAGE level by default. It really isn’t hard to change to a row compression version.
Also note that compressing a clustered index does NOT compress all non-clustered indexes, therefore this script does all indexes on each table.
Well here’s the script… I hope it helps anyone who needs to compress their tables. Note that if you want to run compression then if you compress only the Clustered Index then this does NOT compress non-clustereds… therefore you need to do the entire table and all indexes, which is what this script achieves…
declare @compress table
id int identity,
insert into @compress
select distinct 'alter index ' + i.name + ' on [' + s.name + '].[' + o.name + '] rebuild with (data_compression = page)',
from sys.indexes i
join sys.objects o
on i.object_id = o.object_id
join sys.schemas s
on o.schema_id = s.schema_id
where i.type > 0
and o.is_ms_shipped = 0
/* use the below if you want specific schema or table */
--and s.name = 'sales'
--and o.name = 'salesOrderDetail'
order by i.type
declare @counter int = 1, @sql varchar(max)
while @counter <= (select max(id) from @compress)
select @sql = enableCommand
where id = @counter
select @counter += 1
You may not want to compress all your tables, therefore maybe limit this to only certain names or ones with specific rowcounts or data size… so I've left the option there to amend for specific tables or even schemas.