SQL Server 2008 Compression T/SQL Script

Here is a script that I wrote to try to see how SQL Server 2008 compression would work against all tables in a given database. You can try to see how PAGE vs ROW compression works and if you want to try it out on your dev environment, you can uncomment the "exec (sql) " statment. I haven't done hardly any testing on this, so it may be buggy, so use at your own risk and maybe just use it and/or extend it. It is nice to be able to estimate compression or be able to actually apply it to every table in a database. Let me know how to improve/extend it. Good luck! Also, you will need to change the SELECT statement to suit your purposes.

 

DECLARE @schema    nvarchar(100)

DECLARE @tablename nvarchar(100)

DECLARE @sql nvarchar (500)

DECLARE c1 CURSOR READ_ONLY

FOR

SELECT top 10 TABLE_NAME,TABLE_SCHEMA name FROM INFORMATION_SCHEMA.TABLES --REMOVE TOP 10, WHERE CLAUSE, ETC to suit your purposes

WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_name='salesorderheader'   

ORDER BY TABLE_NAME

OPEN c1

FETCH NEXT FROM c1

INTO @tablename, @schema

WHILE @@FETCH_STATUS = 0

BEGIN

--test both types of compression

--exec sp_estimate_data_compression_savings 'sales', 'salesorderheader', NULL, NULL, 'PAGE'

--exec sp_estimate_data_compression_savings @schema, @tablename, NULL, NULL, 'ROW'

exec sp_estimate_data_compression_savings @schema, @tablename, NULL, NULL, 'PAGE'

--can compress/uncompress tables with PAGE or ROW if you want,

--need to comment out the command you want AND the 'exec' statement :)

--set @sql = 'ALTER TABLE ' +@schema +'.'+ @tablename + ' REBUILD WITH (DATA_COMPRESSION = NONE)'

set @sql = 'ALTER TABLE ' +@schema +'.'+ @tablename + ' REBUILD WITH (DATA_COMPRESSION = PAGE)'

--set @sql = 'ALTER TABLE ' +@schema +'.'+ @tablename + ' REBUILD WITH (DATA_COMPRESSION = ROW)'

--print @sql

--exec (@sql) --UNCOMMENT THIS IF YOU WANT IT TO WORK!!! :)

FETCH NEXT FROM c1

INTO @tablename, @schema

END

CLOSE c1

DEALLOCATE c1