Wednesday, June 10, 2009

Table Compression Cheat

Creating a table with compression...
CREATE TABLE dbo.testtable (testcolumn1 char(1000), testcolumn2 int) WITH (DATA_COMPRESSION = PAGE);

Changing a table to take advantage of compression functionality...
ALTER TABLE dbo.testtable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); 

Want to implement table compression on all tables in a sql 2008 db?
Here's dynamic sql to build those commands...

page compression...
SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'


row compression...
SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);' FROM sys.objects where TYPE = 'u'


and to remove it again...
SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.objects where TYPE = 'u'

No comments: