0 Database compression

Database compression is the SQL Server 2008 Enterprise ONLY features. MSSQL offers 2 type of the data compression. Row compression and Page compression.

To estimate how much space you can save by running sp_estimate_data_compression_savings. The store procedure takes 5 parameters: Schema, object name, index name, partition name, compression type( Null, Page or row)

1. EXEC sp_estimate_data_compression_savings 'dbo', 'TB_1', NULL, NULL, 'ROW' ;
2. GO

We can check the current table compression level using below query

1. select st.name,sp.index_id,si.name,sp.rows,sp.data_compression,sp.data_compression_desc
2. from sys.tables st inner join sys.partitions sp on (st.object_id = sp.object_id )
3. join  sys.indexes si on (si.object_id = sp.object_id and si.index_id = sp.index_id)
4. where st.name='TB_1';

Obtain the table size before the compression

1. sp_spaceused 'TB_1'

Change to the row compression

1. Alter table dbo.TB_1 REBUILD with (DATA_COMPRESSION =ROW)

Table size after row compression

Change to the page compression

1. Alter table dbo.TB_1 REBUILD with (DATA_COMPRESSION =PAGE)

If you want to uncompressing the data, you can use DATA_COMPRESSION = NONE option

1. Alter table dbo.TB_1 REBUILD  with (DATA_COMPRESSION =NONE)

After the data compression is being enable, if you query the sys.dm_db_persisted_sku_features, you will see you have use this enterprise edition feature. By checking this DMV, we can verify whether we have use certain features only available on the higher edition. This is important if you need to do the migration or even just try to restore your production database which is running on the Enterprise version to your development environment which may only running the standard edition.

1. SELECT * FROM sys.dm_db_persisted_sku_features ;

Reference & Future reading

Data Compression: Strategy, Capacity Planning and Best Practices http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx